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, -- IOH.ioh_dc_flag, 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, -- IOH.ioh_dc_flag, 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 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.