Can someone tell me how to replace this UNION statement by simple SELECT Statements inside a CURSOR declare
EXEC SQL
DECLARE CRSR1 CURSOR FOR
SELECT EMP_NBR
, EMP_CONT
, CRT_TS
, TYPE_IND
, FLG
FROM TABLEA
WHERE EMP_NBR = :WS-EMP-NBR
AND TYPE_IND IN ('CL','TI','RM','CP','FA')
AND FLG = 'Y'
AND CRT_TS > :WS-LOW-RUN-TS
AND CRT_TS < :WS-HIGH-RUN-TS
UNION
SELECT A.EMP_NBR
, A.EMP_CONT
, A.CRT_TS
, A.TYPE_IND
, A.FLG
FROM TABLEA A
,TABLEB B
WHERE EMP_NBR = :WS-EMP-NBR
AND A.TYPE_IND IN ('CL','TI','RM','CP','FA')
AND A.FLG = 'Y'
AND A.TYPE_IND = B.TYPE_IND
AND B.LAST_UPD_TS > :WS-LOW-RUN-TS
AND B.LAST_UPD_TS < :WS-HIGH-RUN-TS
UNION
SELECT A.EMP_NBR
, A.EMP_CONT
, A.CRT_TS
, A.TYPE_IND
, A.FLG
FROM TABLEA A
, TABLEC B
WHERE A.EMP_NBR = :WS-EMP-NBR
AND A.TYPE_IND IN ('CL','TI','RM','CP','FA')
AND A.FLG = 'Y'
AND A.EMP_NBR = B.EMP_NBR
AND A.EMP_CONT = B.EMP_CONT
AND B.FLG = 'N'
AND B.NEW_FLG = 'Y'
AND B.AUD_CRT_TS > :WS-LOW-RUN-TS
AND B.AUD_CRT_TS < :WS-HIGH-RUN-TS
Change your select clause to have DISTINCT and WHERE Clause as below and see if it works as its not tested
Code:
SELECT DISTINCT
EMP_NBR
, EMP_CONT
, CRT_TS
, TYPE_IND
, FLG
FROM TABLEA
,,TABLEB B
WHERE
(EMP_NBR = :WS-EMP-NBR
AND TYPE_IND IN ('CL','TI','RM','CP','FA')
AND FLG = 'Y'
AND CRT_TS > :WS-LOW-RUN-TS
AND CRT_TS < :WS-HIGH-RUN-TS )
OR
(EMP_NBR = :WS-EMP-NBR
AND A.TYPE_IND IN ('CL','TI','RM','CP','FA')
AND A.FLG = 'Y'
AND A.TYPE_IND = B.TYPE_IND
AND B.LAST_UPD_TS > :WS-LOW-RUN-TS
AND B.LAST_UPD_TS < :WS-HIGH-RUN-TS)
OR
( A.EMP_NBR = :WS-EMP-NBR
AND A.TYPE_IND IN ('CL','TI','RM','CP','FA')
AND A.FLG = 'Y'
AND A.EMP_NBR = B.EMP_NBR
AND A.EMP_CONT = B.EMP_CONT
AND B.FLG = 'N'
AND B.NEW_FLG = 'Y'
AND B.AUD_CRT_TS > :WS-LOW-RUN-TS
AND B.AUD_CRT_TS < :WS-HIGH-RUN-TS )
You need to check performance of this query as compared to previous one.
Hi,
Thanks a lot for your help. It worked, however i didnt checked the performance. I have a another query to be solved. This is the query.
I have added 'SYSTEM' and 'USER' to the SELECT statments. Could you please help
EXEC SQL
DECLARE CRSR1 CURSOR FOR
SELECT EMP_NBR
, EMP_CONT
, CRT_TS
, TYPE_IND
, FLG
, 'SYSTEM'
FROM TABLEA
WHERE EMP_NBR = :WS-EMP-NBR
AND TYPE_IND IN ('CL','TI','RM','CP','FA')
AND FLG = 'Y'
AND CRT_TS > :WS-LOW-RUN-TS
AND CRT_TS < :WS-HIGH-RUN-TS
UNION
SELECT A.EMP_NBR
, A.EMP_CONT
, A.CRT_TS
, A.TYPE_IND
, A.FLG
FROM TABLEA A
,TABLEB B
,SYSTEM'
WHERE EMP_NBR = :WS-EMP-NBR
AND A.TYPE_IND IN ('CL','TI','RM','CP','FA')
AND A.FLG = 'Y'
AND A.TYPE_IND = B.TYPE_IND
AND B.LAST_UPD_TS > :WS-LOW-RUN-TS
AND B.LAST_UPD_TS < :WS-HIGH-RUN-TS
UNION
SELECT A.EMP_NBR
, A.EMP_CONT
, A.CRT_TS
, A.TYPE_IND
, A.FLG
, 'USER'
FROM TABLEA A
, TABLEC B
WHERE A.EMP_NBR = :WS-EMP-NBR
AND A.TYPE_IND IN ('CL','TI','RM','CP','FA')
AND A.FLG = 'Y'
AND A.EMP_NBR = B.EMP_NBR
AND A.EMP_CONT = B.EMP_CONT
AND B.FLG = 'N'
AND B.NEW_FLG = 'Y'
AND B.AUD_CRT_TS > :WS-LOW-RUN-TS
AND B.AUD_CRT_TS < :WS-HIGH-RUN-TS
.jpg)
