Teradata Spool issue due to several union

I am wondering how to solve teradata query with several unions that causes spool issue.

let say.

single Query1 is executed and completes 11 seconds.
single Query2 is executed and completes 13 seconds.
single Query3 is executed and completes 15 seconds.
single Query4 is executed and completes 18 seconds.

but using union..

Query1
union
Query2
union
Query3
union
Query4

gives around 20 mins or more to complete and causes spool issues.

is there a workaround on this issue?

note each query will have its own subqueries, inner join tables, etc.

Any suggestions.

Update..... Couldnt fit in the whole query .. so cutting down to one union. but in the original query it contains four unions.

--- Attached the long query ----

SELECT * FROM 
Apple.SPOUD_EN SPE 
INNER JOIN Apple.FOUD TNT ON SPE_ORG_TNT_NO = ORG_TNT_ID 
AND SPE_CHG_EFF_STRT_DT <= '2024/07/31' 
AND SPE_CHG_EFF_END_DT >= '2024/07/31' AND SPE_CHG_EFF_END_DT = (
SELECT MAX(SPE2.SPE_CHG_EFF_END_DT) FROM Apple.SPOUD_EN SPE2 WHERE 
WHERE 
SPE2.SPE_ORG_TNT_NO = SPE.SPE_ORG_TNT_NO 
AND SPE2.SPE_CHG_EFF_STRT_DT <= '2024/07/31' 
AND SPE2.SPE_CHG_EFF_END_DT >= '2024/07/31'
) 
AND (
NOT (
SPE.DEL_STS_DT IS NOT NULL 
AND (SPE.DEL_STS_DT <= '2024/07/31')
)
) 
AND TNT.EFF_DT <= '2024/07/31' 
AND TNT.END_DT >= '2024/07/31' 
AND TNT.END_DT = (
SELECT 
MAX(TNT2.END_DT) 
FROM 
Apple.FOUD TNT2 
WHERE 
TNT2.ORG_TNT_ID = TNT.ORG_TNT_ID 
AND TNT2.EFF_DT <= '2024/07/31' 
AND TNT2.END_DT >= '2024/07/31'
) 
LEFT JOIN (
SELECT 
PART_ROLL_ID, 
KIDS_ROLL_ID 
FROM 
Apple.ROLL_HAIRCURL_DLY R1 
WHERE 
R1.PART_HILLS_TYP_ID = 00009 
AND R1.ROLL_HAIRCURL_STRT_DT <= '2024/08/31' 
AND R1.ROLL_HAIRCURL_END_DT >= '2024/08/31' 
AND R1.ROLL_HAIRCURL_END_DT = (
SELECT 
MAX (R2.ROLL_HAIRCURL_END_DT) 
FROM 
Apple.ROLL_HAIRCURL_DLY R2 
WHERE 
R2.PART_HILLS_TYP_ID = 00009 
AND R2.ROLL_HAIRCURL_STRT_DT <= '2024/08/31' 
AND R2.ROLL_HAIRCURL_END_DT >= '2024/08/31' 
AND R2.PART_ROLL_ID = R1.PART_ROLL_ID 
AND R2.KIDS_ROLL_ID = R1.KIDS_ROLL_ID
)
) rp ON TNT.ORG_TNT_ID = rp.KIDS_ROLL_ID 
LEFT JOIN (
SELECT 
PART_ROLL_ID, 
KIDS_ROLL_ID 
FROM 
Apple.ROLL_HAIRCURL_DLY R1 
WHERE 
R1.PART_HILLS_TYP_ID = 00006 
AND R1.ROLL_HAIRCURL_STRT_DT <= '2024/08/31' 
AND R1.ROLL_HAIRCURL_END_DT >= '2024/08/31' 
AND R1.ROLL_HAIRCURL_END_DT = (
SELECT 
MAX (R2.ROLL_HAIRCURL_END_DT) 
FROM 
Apple.ROLL_HAIRCURL_DLY R2 
WHERE 
R2.PART_HILLS_TYP_ID = 00006 
AND R2.ROLL_HAIRCURL_STRT_DT <= '2024/08/31' 
AND R2.ROLL_HAIRCURL_END_DT >= '2024/08/31' 
AND R2.PART_ROLL_ID = R1.PART_ROLL_ID 
AND R2.KIDS_ROLL_ID = R1.KIDS_ROLL_ID
)
) rp2 ON TNT.ORG_TNT_ID = rp2.KIDS_ROLL_ID 
LEFT JOIN (
SELECT 
T1.PROC_CAR_CD, 
T1.ORG_TNT_CNTRL_GRP_ID, 
T1.SURGI_ORG_TNT_NO, 
T1.GOOD_TOY_NO, 
T1.GAAP_TYP_CD, 
T1.BKING_Paper_CD, 
SUM(T1.CLS_BALLA_MURU) AS CLS_BALLA_MURU 
FROM 
(
SELECT 
PROC_CAR_CD, 
ORG_TNT_CNTRL_GRP_ID, 
SURGI_ORG_TNT_NO, 
GOOD_TOY_NO, 
GAAP_TYP_CD, 
BKING_Paper_CD, 
CLS_BALLA_MURU, 
ROW_NUMBER() OVER (
PARTITION BY PROC_CAR_CD, 
ORG_TNT_CNTRL_GRP_ID, 
SURGI_ORG_TNT_NO, 
GOOD_TOY_NO, 
BKING_Paper_CD, 
GAAP_TYP_CD, 
CNTRPRTY_ORG_TNT_CNTRL_GRP_ID, 
CNTRPRTY_SURGI_ORG_TNT_NO, 
EGL_PROJ_ID, 
EGL_PROD_ID, 
GL_ENTRY_SEG_ID, 
EGL_CHNL_ID 
ORDER BY 
AS_AT_BALL_DT DESC
) AS THEROW_NUMBER 
FROM 
Apple.EGL_ACCT_BALL_DTL, 
Apple.SPOUD_EN BSPE 
WHERE 
SURGI_ORG_TNT_NO = BSPE.SPE_ORG_TNT_NO 
AND BSPE.SPE_CHG_EFF_STRT_DT <= '2024/07/31' 
AND BSPE.SPE_CHG_EFF_END_DT >= '2024/07/31' 
AND BSPE.SPE_CHG_EFF_END_DT = (
SELECT 
MAX(SPE3.SPE_CHG_EFF_END_DT) 
FROM 
Apple.SPOUD_EN SPE3 
WHERE 
SPE3.SPE_ORG_TNT_NO = BSPE.SPE_ORG_TNT_NO 
AND SPE3.SPE_CHG_EFF_STRT_DT <= '2024/07/31' 
AND SPE3.SPE_CHG_EFF_END_DT >= '2024/07/31'
) 
AND (
NOT (
BSPE.DEL_STS_DT IS NOT NULL 
AND (BSPE.DEL_STS_DT < '2023/01/31')
)
) 
AND (
BSPE.NAT_FNCL_RPT_STND_APPLECOBOL = 'Y' 
OR BSPE.NAT_FNCL_RPT_STND_APPLECOBOL = 'N'
) 
AND GAAP_TYP_CD IN (
'Coconuts','Pineapple','Grapes'
) 
AND AS_AT_BALL_DT <= '2024/07/31'
) T1 
WHERE 
T1.THEROW_NUMBER = 1 
GROUP BY 
T1.PROC_CAR_CD, 
T1.ORG_TNT_CNTRL_GRP_ID, 
T1.SURGI_ORG_TNT_NO, 
T1.GOOD_TOY_NO, 
T1.GAAP_TYP_CD, 
T1.BKING_Paper_CD
) LATEST_BALL_DTL ON LATEST_BALL_DTL.SURGI_ORG_TNT_NO = SPE.SPE_ORG_TNT_NO 
LEFT JOIN Apple.RPT_LIN_NO_HILLS GLACT ON LATEST_BALL_DTL.GOOD_TOY_NO = GLACT.GOOD_TOY_NO 
AND GLACT.RPT_TYP_ID = 'B/S1' 
AND GLACT.SNAP_DT = '2024/08/31' 
AND GLACT.RPT_LIN_NO IN (
8888....
) 
LEFT JOIN Apple.LOCL_ORG_TNT_SURGI_RT LLRR ON LLRR.ORG_TNT_NO = TNT.ORG_TNT_ID 
AND LLRR.LOCL_Paper_CD = TNT.RPT_Paper_CD 
AND LLRR.RT_STS_CD = 'O' 
AND LLRR.BK_Paper_CD = LATEST_BALL_DTL.BKING_Paper_CD 
AND LLRR.Hotel_EFF_STRT_DT <= '2024/07/31' 
AND LLRR.Hotel_EFF_END_DT >= '2024/07/31' 
AND LLRR.Hotel_EFF_STRT_DT = (
select 
Max (LLRR1.Hotel_EFF_STRT_DT) 
from 
Apple.LOCL_ORG_TNT_SURGI_RT LLRR1 
where 
LLRR1.ORG_TNT_NO = TNT.ORG_TNT_ID 
AND LLRR1.LOCL_Paper_CD = TNT.RPT_Paper_CD 
AND LLRR1.RT_STS_CD = 'O' 
AND LLRR1.BK_Paper_CD = LATEST_BALL_DTL.BKING_Paper_CD 
AND LLRR1.Hotel_EFF_STRT_DT <= '2024/07/31' 
AND LLRR1.Hotel_EFF_END_DT >= '2024/07/31'
) 
LEFT JOIN Apple.GLBL_APPLECOBOL_Paper_RT Cooler ON Cooler.Paper_CD = LATEST_BALL_DTL.BKING_Paper_CD 
AND Cooler.RT_TYP_CD = 'A' 
AND Cooler.RT_ID = 'GREEN' 
AND Cooler.APPLECOBOL_RPT_RT_CD = 'C' 
AND Cooler.FROM_ORGNL_Paper_CD IS NULL 
AND Cooler.RT_EFF_STRT_DT <= '2024/07/31' 
AND Cooler.RT_EFF_END_DT >= '2024/07/31' 
AND Cooler.FREQ_ENTRY_CD = 'M' 
AND Cooler.YR_NO = extract(
year 
from 
'2024/07/31'
) 
AND Cooler.MO_NO = extract(
month 
from 
'2024/07/31'
) 
AND Cooler.RT_EFF_END_DT = (
SELECT 
MAX(RT_EFF_END_DT) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT B 
WHERE 
B.RT_EFF_STRT_DT <= '2024/07/31' 
AND B.RT_EFF_END_DT >= '2024/07/31' 
AND B.RT_TYP_CD = 'A' 
AND B.RT_ID = 'GREEN' 
AND B.APPLECOBOL_RPT_RT_CD = 'C' 
AND B.FROM_ORGNL_Paper_CD IS NULL 
AND B.Paper_CD = Cooler.Paper_CD
) 
LEFT JOIN Apple.GLBL_APPLECOBOL_Paper_RT CoolerOfFunctionalMachine ON CoolerOfFunctionalMachine.Paper_CD = TNT.RPT_Paper_CD 
AND CoolerOfFunctionalMachine.RT_TYP_CD = 'A' 
AND CoolerOfFunctionalMachine.RT_ID = 'GREEN' 
AND CoolerOfFunctionalMachine.APPLECOBOL_RPT_RT_CD = 'C' 
AND CoolerOfFunctionalMachine.FROM_ORGNL_Paper_CD IS NULL 
AND CoolerOfFunctionalMachine.RT_EFF_STRT_DT <= '2024/07/31' 
AND CoolerOfFunctionalMachine.RT_EFF_END_DT >= '2024/07/31' 
AND CoolerOfFunctionalMachine.FREQ_ENTRY_CD = 'M' 
AND CoolerOfFunctionalMachine.YR_NO = extract(
year 
from 
'2024/07/31'
) 
AND CoolerOfFunctionalMachine.MO_NO = extract(
month 
from 
'2024/07/31'
) 
AND CoolerOfFunctionalMachine.RT_EFF_END_DT = (
SELECT 
MAX(RT_EFF_END_DT) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_EFF_STRT_DT <= '2024/07/31' 
AND C.RT_EFF_END_DT >= '2024/07/31' 
AND C.RT_TYP_CD = 'A' 
AND C.RT_ID = 'GREEN' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.FROM_ORGNL_Paper_CD IS NULL 
AND C.Paper_CD = CoolerOfFunctionalMachine.Paper_CD
) 
LEFT JOIN Apple.GLBL_APPLECOBOL_Paper_RT INPaper ON INPaper.Paper_CD = LATEST_BALL_DTL.BKING_Paper_CD 
AND INPaper.Paper_CD = INPaper.FROM_ORGNL_Paper_CD 
AND INPaper.RT_TYP_CD = 'A' 
AND INPaper.APPLECOBOL_RPT_RT_CD = 'C' 
AND INPaper.FREQ_ENTRY_CD = 'C' 
AND INPaper.YR_NO = (
SELECT 
MAX(YR_NO) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_TYP_CD = 'A' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.Paper_CD = INPaper.FROM_ORGNL_Paper_CD 
AND C.Paper_CD = C.FROM_ORGNL_Paper_CD
) 
AND INPaper.MO_NO = (
SELECT 
MAX(MO_NO) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_TYP_CD = 'A' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.Paper_CD = INPaper.FROM_ORGNL_Paper_CD 
AND C.Paper_CD = C.FROM_ORGNL_Paper_CD
) 
AND INPaper.RT_EFF_STRT_DT = (
SELECT 
MAX(RT_EFF_STRT_DT) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_TYP_CD = 'A' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.Paper_CD = INPaper.FROM_ORGNL_Paper_CD 
AND C.Paper_CD = C.FROM_ORGNL_Paper_CD
) 
LEFT JOIN Apple.LOCL_ORG_TNT_SURGI_RT LR_INPaper ON LR_INPaper.ORG_TNT_NO = TNT.ORG_TNT_ID 
AND LR_INPaper.LOCL_Paper_CD = TNT.RPT_Paper_CD 
AND LR_INPaper.RT_STS_CD = 'O' 
AND LR_INPaper.BK_Paper_CD = INPaper.RT_ID 
AND LR_INPaper.Hotel_EFF_STRT_DT <= '2024/07/31' 
AND LR_INPaper.Hotel_EFF_END_DT >= '2024/07/31' 
LEFT JOIN Apple.GLBL_APPLECOBOL_Paper_RT GR_INPaper ON GR_INPaper.Paper_CD = INPaper.RT_ID 
AND GR_INPaper.RT_TYP_CD = 'A' 
AND GR_INPaper.RT_ID = 'GREEN' 
AND GR_INPaper.APPLECOBOL_RPT_RT_CD = 'C' 
AND GR_INPaper.FROM_ORGNL_Paper_CD IS NULL 
AND GR_INPaper.RT_EFF_STRT_DT <= '2024/07/31' 
AND GR_INPaper.RT_EFF_END_DT >= '2024/07/31' 
AND GR_INPaper.FREQ_ENTRY_CD = 'M' 
AND GR_INPaper.YR_NO = extract(
year 
from 
'2024/07/31'
) 
AND GR_INPaper.MO_NO = extract(
month 
from 
'2024/07/31'
) 
AND GR_INPaper.RT_EFF_END_DT = (
SELECT 
MAX(RT_EFF_END_DT) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_EFF_STRT_DT <= '2024/07/31' 
AND C.RT_EFF_END_DT >= '2024/07/31' 
AND C.RT_TYP_CD = 'A' 
AND C.RT_ID = 'GREEN' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.FROM_ORGNL_Paper_CD IS NULL 
AND C.Paper_CD = GR_INPaper.Paper_CD
) 
WHERE 
(
SPE.NAT_FNCL_RPT_STND_APPLECOBOL = 'Y' 
OR SPE.NAT_FNCL_RPT_STND_APPLECOBOL = 'N'
) 
AND TNT.CNTRL_TNT_NO IN ('78787') 
union
SELECT * FROM 
Apple.SPOUD_EN SPE 
INNER JOIN Apple.FOUD TNT ON SPE_ORG_TNT_NO = ORG_TNT_ID 
AND SPE_CHG_EFF_STRT_DT <= '2023/10/31' 
AND SPE_CHG_EFF_END_DT >= '2023/10/31' AND SPE_CHG_EFF_END_DT = (
SELECT MAX(SPE2.SPE_CHG_EFF_END_DT) FROM Apple.SPOUD_EN SPE2 WHERE 
WHERE 
SPE2.SPE_ORG_TNT_NO = SPE.SPE_ORG_TNT_NO 
AND SPE2.SPE_CHG_EFF_STRT_DT <= '2023/10/31' 
AND SPE2.SPE_CHG_EFF_END_DT >= '2023/10/31'
) 
AND (
NOT (
SPE.DEL_STS_DT IS NOT NULL 
AND (SPE.DEL_STS_DT <= '2023/10/31')
)
) 
AND TNT.EFF_DT <= '2023/10/31' 
AND TNT.END_DT >= '2023/10/31' 
AND TNT.END_DT = (
SELECT 
MAX(TNT2.END_DT) 
FROM 
Apple.FOUD TNT2 
WHERE 
TNT2.ORG_TNT_ID = TNT.ORG_TNT_ID 
AND TNT2.EFF_DT <= '2023/10/31' 
AND TNT2.END_DT >= '2023/10/31'
) 
LEFT JOIN (
SELECT 
PART_ROLL_ID, 
KIDS_ROLL_ID 
FROM 
Apple.ROLL_HAIRCURL_DLY R1 
WHERE 
R1.PART_HILLS_TYP_ID = 00009 
AND R1.ROLL_HAIRCURL_STRT_DT <= '2024/08/31' 
AND R1.ROLL_HAIRCURL_END_DT >= '2024/08/31' 
AND R1.ROLL_HAIRCURL_END_DT = (
SELECT 
MAX (R2.ROLL_HAIRCURL_END_DT) 
FROM 
Apple.ROLL_HAIRCURL_DLY R2 
WHERE 
R2.PART_HILLS_TYP_ID = 00009 
AND R2.ROLL_HAIRCURL_STRT_DT <= '2024/08/31' 
AND R2.ROLL_HAIRCURL_END_DT >= '2024/08/31' 
AND R2.PART_ROLL_ID = R1.PART_ROLL_ID 
AND R2.KIDS_ROLL_ID = R1.KIDS_ROLL_ID
)
) rp ON TNT.ORG_TNT_ID = rp.KIDS_ROLL_ID 
LEFT JOIN (
SELECT 
PART_ROLL_ID, 
KIDS_ROLL_ID 
FROM 
Apple.ROLL_HAIRCURL_DLY R1 
WHERE 
R1.PART_HILLS_TYP_ID = 00006 
AND R1.ROLL_HAIRCURL_STRT_DT <= '2024/08/31' 
AND R1.ROLL_HAIRCURL_END_DT >= '2024/08/31' 
AND R1.ROLL_HAIRCURL_END_DT = (
SELECT 
MAX (R2.ROLL_HAIRCURL_END_DT) 
FROM 
Apple.ROLL_HAIRCURL_DLY R2 
WHERE 
R2.PART_HILLS_TYP_ID = 00006 
AND R2.ROLL_HAIRCURL_STRT_DT <= '2024/08/31' 
AND R2.ROLL_HAIRCURL_END_DT >= '2024/08/31' 
AND R2.PART_ROLL_ID = R1.PART_ROLL_ID 
AND R2.KIDS_ROLL_ID = R1.KIDS_ROLL_ID
)
) rp2 ON TNT.ORG_TNT_ID = rp2.KIDS_ROLL_ID 
LEFT JOIN (
SELECT 
T1.PROC_CAR_CD, 
T1.ORG_TNT_CNTRL_GRP_ID, 
T1.SURGI_ORG_TNT_NO, 
T1.GOOD_TOY_NO, 
T1.GAAP_TYP_CD, 
T1.BKING_Paper_CD, 
SUM(T1.CLS_BALLA_MURU) AS CLS_BALLA_MURU 
FROM 
(
SELECT 
PROC_CAR_CD, 
ORG_TNT_CNTRL_GRP_ID, 
SURGI_ORG_TNT_NO, 
GOOD_TOY_NO, 
GAAP_TYP_CD, 
BKING_Paper_CD, 
CLS_BALLA_MURU, 
ROW_NUMBER() OVER (
PARTITION BY PROC_CAR_CD, 
ORG_TNT_CNTRL_GRP_ID, 
SURGI_ORG_TNT_NO, 
GOOD_TOY_NO, 
BKING_Paper_CD, 
GAAP_TYP_CD, 
CNTRPRTY_ORG_TNT_CNTRL_GRP_ID, 
CNTRPRTY_SURGI_ORG_TNT_NO, 
EGL_PROJ_ID, 
EGL_PROD_ID, 
GL_ENTRY_SEG_ID, 
EGL_CHNL_ID 
ORDER BY 
AS_AT_BALL_DT DESC
) AS THEROW_NUMBER 
FROM 
Apple.EGL_ACCT_BALL_DTL, 
Apple.SPOUD_EN BSPE 
WHERE 
SURGI_ORG_TNT_NO = BSPE.SPE_ORG_TNT_NO 
AND BSPE.SPE_CHG_EFF_STRT_DT <= '2023/10/31' 
AND BSPE.SPE_CHG_EFF_END_DT >= '2023/10/31' 
AND BSPE.SPE_CHG_EFF_END_DT = (
SELECT 
MAX(SPE3.SPE_CHG_EFF_END_DT) 
FROM 
Apple.SPOUD_EN SPE3 
WHERE 
SPE3.SPE_ORG_TNT_NO = BSPE.SPE_ORG_TNT_NO 
AND SPE3.SPE_CHG_EFF_STRT_DT <= '2023/10/31' 
AND SPE3.SPE_CHG_EFF_END_DT >= '2023/10/31'
) 
AND (
NOT (
BSPE.DEL_STS_DT IS NOT NULL 
AND (BSPE.DEL_STS_DT < '2022/04/30')
)
) 
AND (
BSPE.NAT_FNCL_RPT_STND_APPLECOBOL = 'Y' 
OR BSPE.NAT_FNCL_RPT_STND_APPLECOBOL = 'N'
) 
AND GAAP_TYP_CD IN (
'Coconuts','Pineapple','Grapes'
) 
AND AS_AT_BALL_DT <= '2023/10/31'
) T1 
WHERE 
T1.THEROW_NUMBER = 1 
GROUP BY 
T1.PROC_CAR_CD, 
T1.ORG_TNT_CNTRL_GRP_ID, 
T1.SURGI_ORG_TNT_NO, 
T1.GOOD_TOY_NO, 
T1.GAAP_TYP_CD, 
T1.BKING_Paper_CD
) LATEST_BALL_DTL ON LATEST_BALL_DTL.SURGI_ORG_TNT_NO = SPE.SPE_ORG_TNT_NO 
LEFT JOIN Apple.RPT_LIN_NO_HILLS GLACT ON LATEST_BALL_DTL.GOOD_TOY_NO = GLACT.GOOD_TOY_NO 
AND GLACT.RPT_TYP_ID = 'B/S1' 
AND GLACT.SNAP_DT = '2024/08/31' 
AND GLACT.RPT_LIN_NO IN (
8888....
) 
LEFT JOIN Apple.LOCL_ORG_TNT_SURGI_RT LLRR ON LLRR.ORG_TNT_NO = TNT.ORG_TNT_ID 
AND LLRR.LOCL_Paper_CD = TNT.RPT_Paper_CD 
AND LLRR.RT_STS_CD = 'O' 
AND LLRR.BK_Paper_CD = LATEST_BALL_DTL.BKING_Paper_CD 
AND LLRR.Hotel_EFF_STRT_DT <= '2023/10/31' 
AND LLRR.Hotel_EFF_END_DT >= '2023/10/31' 
AND LLRR.Hotel_EFF_STRT_DT = (
select 
Max (LLRR1.Hotel_EFF_STRT_DT) 
from 
Apple.LOCL_ORG_TNT_SURGI_RT LLRR1 
where 
LLRR1.ORG_TNT_NO = TNT.ORG_TNT_ID 
AND LLRR1.LOCL_Paper_CD = TNT.RPT_Paper_CD 
AND LLRR1.RT_STS_CD = 'O' 
AND LLRR1.BK_Paper_CD = LATEST_BALL_DTL.BKING_Paper_CD 
AND LLRR1.Hotel_EFF_STRT_DT <= '2023/10/31' 
AND LLRR1.Hotel_EFF_END_DT >= '2023/10/31'
) 
LEFT JOIN Apple.GLBL_APPLECOBOL_Paper_RT Cooler ON Cooler.Paper_CD = LATEST_BALL_DTL.BKING_Paper_CD 
AND Cooler.RT_TYP_CD = 'A' 
AND Cooler.RT_ID = 'GREEN' 
AND Cooler.APPLECOBOL_RPT_RT_CD = 'C' 
AND Cooler.FROM_ORGNL_Paper_CD IS NULL 
AND Cooler.RT_EFF_STRT_DT <= '2023/10/31' 
AND Cooler.RT_EFF_END_DT >= '2023/10/31' 
AND Cooler.FREQ_ENTRY_CD = 'M' 
AND Cooler.YR_NO = extract(
year 
from 
'2023/10/31'
) 
AND Cooler.MO_NO = extract(
month 
from 
'2023/10/31'
) 
AND Cooler.RT_EFF_END_DT = (
SELECT 
MAX(RT_EFF_END_DT) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT B 
WHERE 
B.RT_EFF_STRT_DT <= '2023/10/31' 
AND B.RT_EFF_END_DT >= '2023/10/31' 
AND B.RT_TYP_CD = 'A' 
AND B.RT_ID = 'GREEN' 
AND B.APPLECOBOL_RPT_RT_CD = 'C' 
AND B.FROM_ORGNL_Paper_CD IS NULL 
AND B.Paper_CD = Cooler.Paper_CD
) 
LEFT JOIN Apple.GLBL_APPLECOBOL_Paper_RT CoolerOfFunctionalMachine ON CoolerOfFunctionalMachine.Paper_CD = TNT.RPT_Paper_CD 
AND CoolerOfFunctionalMachine.RT_TYP_CD = 'A' 
AND CoolerOfFunctionalMachine.RT_ID = 'GREEN' 
AND CoolerOfFunctionalMachine.APPLECOBOL_RPT_RT_CD = 'C' 
AND CoolerOfFunctionalMachine.FROM_ORGNL_Paper_CD IS NULL 
AND CoolerOfFunctionalMachine.RT_EFF_STRT_DT <= '2023/10/31' 
AND CoolerOfFunctionalMachine.RT_EFF_END_DT >= '2023/10/31' 
AND CoolerOfFunctionalMachine.FREQ_ENTRY_CD = 'M' 
AND CoolerOfFunctionalMachine.YR_NO = extract(
year 
from 
'2023/10/31'
) 
AND CoolerOfFunctionalMachine.MO_NO = extract(
month 
from 
'2023/10/31'
) 
AND CoolerOfFunctionalMachine.RT_EFF_END_DT = (
SELECT 
MAX(RT_EFF_END_DT) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_EFF_STRT_DT <= '2023/10/31' 
AND C.RT_EFF_END_DT >= '2023/10/31' 
AND C.RT_TYP_CD = 'A' 
AND C.RT_ID = 'GREEN' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.FROM_ORGNL_Paper_CD IS NULL 
AND C.Paper_CD = CoolerOfFunctionalMachine.Paper_CD
) 
LEFT JOIN Apple.GLBL_APPLECOBOL_Paper_RT INPaper ON INPaper.Paper_CD = LATEST_BALL_DTL.BKING_Paper_CD 
AND INPaper.Paper_CD = INPaper.FROM_ORGNL_Paper_CD 
AND INPaper.RT_TYP_CD = 'A' 
AND INPaper.APPLECOBOL_RPT_RT_CD = 'C' 
AND INPaper.FREQ_ENTRY_CD = 'C' 
AND INPaper.YR_NO = (
SELECT 
MAX(YR_NO) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_TYP_CD = 'A' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.Paper_CD = INPaper.FROM_ORGNL_Paper_CD 
AND C.Paper_CD = C.FROM_ORGNL_Paper_CD
) 
AND INPaper.MO_NO = (
SELECT 
MAX(MO_NO) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_TYP_CD = 'A' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.Paper_CD = INPaper.FROM_ORGNL_Paper_CD 
AND C.Paper_CD = C.FROM_ORGNL_Paper_CD
) 
AND INPaper.RT_EFF_STRT_DT = (
SELECT 
MAX(RT_EFF_STRT_DT) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_TYP_CD = 'A' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.Paper_CD = INPaper.FROM_ORGNL_Paper_CD 
AND C.Paper_CD = C.FROM_ORGNL_Paper_CD
) 
LEFT JOIN Apple.LOCL_ORG_TNT_SURGI_RT LR_INPaper ON LR_INPaper.ORG_TNT_NO = TNT.ORG_TNT_ID 
AND LR_INPaper.LOCL_Paper_CD = TNT.RPT_Paper_CD 
AND LR_INPaper.RT_STS_CD = 'O' 
AND LR_INPaper.BK_Paper_CD = INPaper.RT_ID 
AND LR_INPaper.Hotel_EFF_STRT_DT <= '2023/10/31' 
AND LR_INPaper.Hotel_EFF_END_DT >= '2023/10/31' 
LEFT JOIN Apple.GLBL_APPLECOBOL_Paper_RT GR_INPaper ON GR_INPaper.Paper_CD = INPaper.RT_ID 
AND GR_INPaper.RT_TYP_CD = 'A' 
AND GR_INPaper.RT_ID = 'GREEN' 
AND GR_INPaper.APPLECOBOL_RPT_RT_CD = 'C' 
AND GR_INPaper.FROM_ORGNL_Paper_CD IS NULL 
AND GR_INPaper.RT_EFF_STRT_DT <= '2023/10/31' 
AND GR_INPaper.RT_EFF_END_DT >= '2023/10/31' 
AND GR_INPaper.FREQ_ENTRY_CD = 'M' 
AND GR_INPaper.YR_NO = extract(
year 
from 
'2023/10/31'
) 
AND GR_INPaper.MO_NO = extract(
month 
from 
'2023/10/31'
) 
AND GR_INPaper.RT_EFF_END_DT = (
SELECT 
MAX(RT_EFF_END_DT) 
FROM 
Apple.GLBL_APPLECOBOL_Paper_RT C 
WHERE 
C.RT_EFF_STRT_DT <= '2023/10/31' 
AND C.RT_EFF_END_DT >= '2023/10/31' 
AND C.RT_TYP_CD = 'A' 
AND C.RT_ID = 'GREEN' 
AND C.APPLECOBOL_RPT_RT_CD = 'C' 
AND C.FROM_ORGNL_Paper_CD IS NULL 
AND C.Paper_CD = GR_INPaper.Paper_CD
) 
WHERE 
(
SPE.NAT_FNCL_RPT_STND_APPLECOBOL = 'Y' 
OR SPE.NAT_FNCL_RPT_STND_APPLECOBOL = 'N'
) 
AND TNT.CNTRL_TNT_NO IN ('78787');

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

Teradata Spool issue due to several union - Stack Overflow

与本文相关的文章

评论列表(0)