|
View previous topic :: View next topic
|
| Author |
Message |
Vithiyalan
New User
Joined: 15 Nov 2011
Posts: 6
Location: India
|
|
|
|
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
WITH UR
END-EXEC |
|
| Back to top |
|
 |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
What you have tried so far for this?
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.
Regards,
Chandan |
|
| Back to top |
|
|
Vithiyalan
New User
Joined: 15 Nov 2011
Posts: 6
Location: India
|
|
|
|
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
WITH UR
END-EXEC |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Is SYSTEM & USER are fields or Table names?
I guess its they are fields please confirm. |
|
| Back to top |
|
|
Vithiyalan
New User
Joined: 15 Nov 2011
Posts: 6
Location: India
|
|
|
|
| Its like a character constant that is appended to the end of the result. It is just to identify like from which query the row was picked |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
