[Solved] How to combine three queries?

by


I’m sure you can work this out yourself.. I guess it needs to look something like this.. but you don’t say how you want the data to be combined so I’ve just just a cross product (the WHERE 1=1) of the last data set to a union of the first two

with PH AS(
    SELECT CHRD,CHWO,CHSE,CHST,CHVR,CHFV,CHRD,
    ROW_NUMBER () OVER(PARTITION BY CHWO ORDER BY CHSE,CHVR  DESC) TEMP
    FROM WRPD.WSCL
    WHERE CHAJ > '20180901' 
    AND CHST ='R' 
    AND CHSTB in ('L1', 'R2')
    --ORDER BY CHSE 
), DT AS(
    SELECT CHRD,CHWO,CHSE,CHST,CHVR,CHFV,CHRD,
    ROW_NUMBER () OVER(PARTITION BY CHWO ORDER BY CHSE,CHVR  DESC) TEMP
    FROM WRPDAT.WSCLHP
    WHERE CHAJDT > '20180901' 
    AND CHST IN ('P','A') 
    AND CHSTB in ('L1', 'R2')
)
, U AS (
    SELECT * 
    FROM PH A
    WHERE A.TEMP=1
    UNION ALL
    SELECT *
    FROM DT
)
, Z AS
(
    SELECT
    PARTd1, ACTDd1, DMRE,DMDE,DLRO,DORD,WVIN,RCDT,
    CHAR(DATE(SUBSTR(CHAR(RCDT),1,4) ||'-'||
        SUBSTR(CHAR(RCDT),5,2) ||'-'||
       SUBSTR(CHAR(RCDT),7,2)), USA)
    AS "Conversion",
    SUM(QTY) AS Shipments, RCDT-DORD AS Ship_Days
    FROM PSPD.ZPSLSP, PSPD.ZPSLMA, dlpd.drdm,WRPD.WSCL
    WHERE PARTd1 = PARTpm
    AND DELR = DMDE
    AND   ACTDd1 between '201801' and '201810'
    AND OTYPd1 NOT IN ('T','Z','W')

    AND DMTY = 'RD'
    and partpm like '21101%' 
    and substr(delr,3,3) not between '390' and '399'

    and QTYS >0
    and  CHFV = WVIN

    Group By PARTd1, ACTDd1, DMDE, DLRO, DORD,RCDT, DMRE,WVIN
)
SELECT U.*, Z.* FROM U, Z WHERE CHFV = WVIN
ORDER BY TEMP, PARTd1, ACTDd1

4

solved How to combine three queries?