| Join 2 select statements [message #648396] |
Tue, 23 February 2016 00:49  |
 |
kar116
Messages: 3
Registered: February 2016
Location: bangalore
|
Junior Member |
|
|
hi,
I Have below 2 select statement.
1) SELECT CRE.pl_num AS,
( DEB.dt - CRE.ct ) AS AMNT
FROM (SELECT ULM.ulm_no AS PL_NUM,
SUM(IOH.ioh_os_amt) AS CT
FROM ezwhtx.itds_os_header IOH
join ezwhtx.upds_level_m ULM
ON ULM.ulm_no = IOH.ioh_ulm_no
WHERE IOH.ioh_dc_flag = 'C'
AND ioh_cust_catg_id = '001'
AND ioh_clc_no IS NULL
GROUP BY ULM.ulm_no) CRE
cross join (SELECT ULM.ulm_no PL_NUM,
SUM(IOH.ioh_os_amt) DT
FROM ezwhtx.itds_os_header IOH
join ezwhtx.upds_level_m ULM
ON ULM.ulm_no = IOH.ioh_ulm_no
WHERE IOH.ioh_dc_flag = 'D'
AND ioh_cust_catg_id = '001'
AND ioh_clc_no IS NULL
GROUP BY ULM.ulm_no) DEB
WHERE CRE.pl_num = DEB.pl_num
ORDER BY CRE.pl_num;
if i run the above select statement, i will get the result as PL_NUM & AMNT information.
2) SELECT ULM.ulm_no,
ULM.ulm_typ,
UPD.upd_ref_no,
ULM.ulm_insrd_name,
ULM.ulm_cancel_dt,
ULM.ulm_status,
ULM.ulm_cont_ref_id
FROM ezwhtx.upds_level_m ULM
join ezwhtx.uds_party_dtls UPD
ON ULM.ulm_agent_id = UPD.upd_id
WHERE ULM.ulm_status IN( 'CAN', 'CP' )
AND ULM.ulm_typ = 'P'
ORDER BY ULM.ulm_no;
if i run the above select statement, i will get the result as ULM_NO,ULM_TYP,UPD_REF_NO,ULM_INSRD_NAME,ULM_CANCEL_DT,ULM_STATUS,ULM_CONT_REF_ID.
Now my question i want join above 2 select statements and want the result should be
ULM_NO,ULM_TYP,UPD_REF_NO,ULM_INSRD_NAME,ULM_CANCEL_DT,ULM_STATUS,ULM_CONT_REF_ID, AMNT
How can i do that for a best way? please send me the final select statement.
|
|
|
|
| Re: Join 2 select statements [message #648397 is a reply to message #648396] |
Tue, 23 February 2016 00:58  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Quote:Now my question i want join above 2 select statements and want the result should be
What is the relation between each row of each select?
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
