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.
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
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
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