UNIX for Dummies Questions & Answers

I have two queries

query1

select IDW_SITE_NAME REGION,a.location_sid,a.CALENDAR_SID,count(*) from idw.vod_stream a, IDW_REF_DATA b
where a.location_sid = b.location_sid and a.calendar_sid between 20110901 and 20110915 --this is start date parameter and end date
parameter
and a.location_sid = 190 --this is location id parameter
and HAS_ERROR = 'N'
group by IDW_SITE_NAME,a.location_sid,a.CALENDAR_SID
order by a.location_sid,a.CALENDAR_SID

output

REGION LOCATION_SID CALENDAR_SID IDW COUNT
AUSTIN 190 20110901 110557
AUSTIN 190 20110902 132851
AUSTIN 190 20110903 168656

query2

select REGION,a.location_sid,a.CALENDAR_SID,count(*) from sda_vod_stream a, SDA_DIVISIONS b
where a.location_sid = b.location_sid and a.calendar_sid between 20110901 and 20110915
and a.location_sid = 190
group by REGION,a.location_sid,a.CALENDAR_SID
order by a.location_sid,a.CALENDAR_SID

output

REGION LOCATION_SID CALENDAR_SID SDA COUNT
AUSTIN 190 20110901 110559
AUSTIN 190 20110902 132851
AUSTIN 190 20110903 168659

both queries will be getting executed on separate database
e.g
query1-->executes on database1
query2-->executes on database2


now my requirement is that

here is final output

REGION LOCATION_SID CALENDAR_SID IDW COUNT SDA COUNT DIFF
AUSTIN 190 20110901 110557 110559 -2
AUSTIN 190 20110902 132851 132851 0
AUSTIN 190 20110903 168656 168659 -3

here difference = query1.count - query2.count

how can I produce a final output CSV /XLS file

I can generate above report file using oracle,but here both databases are different

I'm thinkning to copy above SQL in a shell script and can generate a report as oracle is based on UNIX

parameters

in above both query the changing parameter is
1. 20110901 this is start date parameter
2. 20110915 end date parameter
3. 190 this is location id

Please let me know if anybody has different solution for this ?

Please edit your post to include code tags. I've just spent ten minutes trying to clean up your other thread!

Here is how:



Thanks.

I have two queries

query1

Code:

 
select IDW_SITE_NAME REGION,a.location_sid,a.CALENDAR_SID,count(*) from idw.vod_stream a, IDW_REF_DATA b
where a.location_sid = b.location_sid and a.calendar_sid between 20110901 and 20110915 --this is start date parameter and end date 
parameter
and a.location_sid = 190 --this is location id parameter
and HAS_ERROR = 'N'
group by IDW_SITE_NAME,a.location_sid,a.CALENDAR_SID
order by a.location_sid,a.CALENDAR_SID

output

REGION LOCATION_SID CALENDAR_SID IDW COUNT
AUSTIN 190 20110901 110557
AUSTIN 190 20110902 132851
AUSTIN 190 20110903 168656

query2

Code:

 
select REGION,a.location_sid,a.CALENDAR_SID,count(*) from sda_vod_stream a, SDA_DIVISIONS b
where a.location_sid = b.location_sid and a.calendar_sid between 20110901 and 20110915
and a.location_sid = 190
group by REGION,a.location_sid,a.CALENDAR_SID
order by a.location_sid,a.CALENDAR_SID

output

REGION LOCATION_SID CALENDAR_SID SDA COUNT
AUSTIN 190 20110901 110559
AUSTIN 190 20110902 132851
AUSTIN 190 20110903 168659

both queries will be getting executed on separate database
e.g
query1-->executes on database1
query2-->executes on database2


now my requirement is that

here is final output

REGION LOCATION_SID CALENDAR_SID IDW COUNT SDA COUNT DIFF
AUSTIN 190 20110901 110557 110559 -2
AUSTIN 190 20110902 132851 132851 0
AUSTIN 190 20110903 168656 168659 -3

here difference = query1.count - query2.count

how can I produce a final output CSV /XLS file

I can generate above report file using oracle,but here both databases are different

I'm thinkning to copy above SQL in a shell script and can generate a report as oracle is based on UNIX

parameters

in above both query the changing parameter is
1. 20110901 this is start date parameter
2. 20110915 end date parameter
3. 190 this is location id

Please let me know if anybody has different solution for this ?

I would recommend creating a db link between those databases and do the required operation via sql code.

It should be much cleaner and easier then using shell.
Second choice would be using perl with DBI module.

Regards
Peasant.

I agree with Peasant. This is why federation exists. Work smarter not harder

I agree with Peasant Too