Nesting Union Query in Join/Select

[therese1]

Hi I have set up my query as per below, I am stuck on where to put union query so I can filter it... code is ok until I add this.

Code Select Expand

select distinct
a.PROP_NO,a.AMDT_SEQ,a.AMDT_SEQ_MAX_FLG,a.CTRT_EXP_DT,b.*,c.CTRT_SRC

from DMS_PRC_MN a
join (
    select CTRT_CUST_CD,ORG_LOC_DEF_CD,DEST_LOC_DEF_CD, count(*) as qty
    from DMS_PRC_MN
    group by CTRT_CUST_CD,ORG_LOC_DEF_CD,DEST_LOC_DEF_CD
    having count(*) > 1
) b on a.CTRT_CUST_CD = b.CTRT_CUST_CD and a.ORG_LOC_DEF_CD = b.ORG_LOC_DEF_CD and a.DEST_LOC_DEF_CD = b.DEST_LOC_DEF_CD
select (CTRT_SRC_CD, PROP_NO
from DWS_RP_HDR
union ALL
select CTRT_SRC_CD, PROP_NO
from DWS_SP_HDR) c on a.PROP_NO=c.PROP_NO
where a.CTRT_EXP_DT>=sysdate

[therese1]

to better explain below queries work on their own but I want to merge them together, PROP_NO would like them, I want to show CTR_SRC_CD from HDRs for PROP_NO

Union query SQL

Code Select Expand

select CTRT_SRC_CD, PROP_NO
from DWS_RP_HDR
union ALL
select CTRT_SRC_CD, PROP_NO
from DWS_SP_HDR

Select SQL
select distinct
a.PROP_NO,a.AMDT_SEQ,a.AMDT_SEQ_MAX_FLG,a.CTRT_EXP_DT,b.*,c.CTRT_SRC

from DMS_PRC_MN a
join (
    select CTRT_CUST_CD,ORG_LOC_DEF_CD,DEST_LOC_DEF_CD, count(*) as qty
    from DMS_PRC_MN
    group by CTRT_CUST_CD,ORG_LOC_DEF_CD,DEST_LOC_DEF_CD
    having count(*) > 1
) b on a.CTRT_CUST_CD = b.CTRT_CUST_CD and a.ORG_LOC_DEF_CD = b.ORG_LOC_DEF_CD and a.DEST_LOC_DEF_CD = b.DEST_LOC_DEF_CD

where a.CTRT_EXP_DT>=sysdate