Home » SQL & PL/SQL » SQL & PL/SQL » Joining results of 2 queries (Oracle 9i)
Joining results of 2 queries [message #352845] Thu, 09 October 2008 21:59 Go to next message
kannanmugundan
Messages: 3
Registered: October 2008
Location: India
Junior Member

Hi


Below are my 2 SQL queries, which acts on same table with different condition.... Now i need to join the results into a single result set.....
so please advise, I am new to Oracle, so please explain in detail..

Query 1
===================

SELECT repm_dlr_no, dorg_org_dsctn, COUNT (1) AS "GOLD_RO_CNT"
FROM rcrepm_tb, cadorg_tb
WHERE repm_spc_msg_dsctn LIKE 'GOLD%'
AND (TO_CHAR (repm_ro_dtime, 'YYYYMMDD') BETWEEN '20080801' AND '20080930'
)
AND dorg_cmpn_no(+) = repm_cmpn_no
AND dorg_corp_no(+) = repm_corp_no
AND dorg_dlr_no(+) = repm_dlr_no
AND dorg_sctn_type(+) = 'R'
GROUP BY repm_dlr_no, dorg_org_dsctn

order by repm_dlr_no;

Query 2
==========================
SELECT repm_dlr_no, COUNT (1) AS "RO_CLSD_SAME_DAY"
FROM rcrepm_tb
WHERE TO_CHAR (repm_ro_dtime, 'YYYYMMDD') =
TO_CHAR (repm_clse_dtime, 'YYYYMMDD')
AND (TO_CHAR (repm_ro_dtime, 'YYYYMMDD') BETWEEN '20080801' AND '20080930'
)
AND repm_spc_msg_dsctn LIKE 'GOLD%'
GROUP BY repm_dlr_no
order by repm_dlr_no;



Currently the output of first query is like

E1200 FROSTEES EXPORT (INDIA) P LTD 479.00
E1201 J.J. Automotive LTD. 444.00
E1202 M/S DURGA HYUNDAI 107.00
E1203 RUDRA AUTOMOBILES P. LTD. 221.00
E1204 HILLA ENTERPRISE PVT. LTD. 53.00


The result of second Query is like
E1200 324.00
E1201 283.00
E1202 79.00
E1203 170.00
E1204 27.00
E1205 436.00
E1206 19.00




But the required output is


E1200 FROSTEES EXPORT (INDIA) P LTD 479.00 324.00
E1201 J.J. Automotive LTD. 444.00 283.00
E1202 M/S DURGA HYUNDAI 107.00 79.0
E1203 RUDRA AUTOMOBILES P. LTD. 221.00 170.00
E1204 HILLA ENTERPRISE PVT. LTD. 53.00 27.00





Thanks
Re: Joining results of 2 queries [message #352846 is a reply to message #352845] Thu, 09 October 2008 22:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Welcome to Orafaq forum world .

Please ensure that your posts are formatted to get the reply from next time.

One solution will be to use correlated subquey ( second query ) with your first query .

Btw , why did you

Quote:
(TO_CHAR (repm_ro_dtime, 'YYYYMMDD') BETWEEN '20080801' AND '20080930'


Instead of using direct date fields ?

Thumbs Up
Rajuvan.
Re: Joining results of 2 queries [message #352849 is a reply to message #352845] Thu, 09 October 2008 22:33 Go to previous messageGo to next message
kannanmugundan
Messages: 3
Registered: October 2008
Location: India
Junior Member

Hi,

Can you explain in detail, or how to modify my query , i read the correlated sub query, but in my case, i am using count and my sub-query returns more than one row, so i may have to add condition , but i am still not clear on it
Re: Joining results of 2 queries [message #352851 is a reply to message #352845] Thu, 09 October 2008 22:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes . you have to add some condition to restring the number of rows in the subquery .

Anyway which field do you feel good to restrict the number of record in your sub query ? Just check your previous outputs Smile

Thumbs Up
Rajuvan.
Re: Joining results of 2 queries [message #352855 is a reply to message #352845] Thu, 09 October 2008 22:57 Go to previous messageGo to next message
kannanmugundan
Messages: 3
Registered: October 2008
Location: India
Junior Member

Hi,


i need to restrict the result based on the column repm_dlr_no (dealer no), but i cannot hardcode the dealer no in my query instead it should be joined with the dealer code used by the outer query and this has to be done for all the repm_dlr_no (dealer no) in the table.


Basically i am trying to get a report generated.



thanks
Re: Joining results of 2 queries [message #352859 is a reply to message #352845] Thu, 09 October 2008 23:05 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


hmhm ..

Have you ever seen the subquery HARDCODED in the above link ? Make an alias and join the dealer code in the subquery ..

Thumbs Up
Rajuvan.
Previous Topic: primary key issue
Next Topic: NOT NULL COLUMN CARRY THE DEFINE VALUE
Goto Forum:
  


Current Time: Fri Dec 02 20:55:29 CST 2016

Total time taken to generate the page: 1.67036 seconds