IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Replace UNION by multiple SELECT Statments inside a cursor?


IBM Mainframe Forums -> HomeWorks & Requests
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Vithiyalan

New User


Joined: 15 Nov 2011
Posts: 6
Location: India

PostPosted: Thu Jan 19, 2012 5:18 pm
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Jan 20, 2012 11:59 am
Reply with quote

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
View user's profile Send private message
Vithiyalan

New User


Joined: 15 Nov 2011
Posts: 6
Location: India

PostPosted: Fri Jan 20, 2012 3:49 pm
Reply with quote

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
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri Jan 20, 2012 4:27 pm
Reply with quote

Is SYSTEM & USER are fields or Table names?

I guess its they are fields please confirm.
Back to top
View user's profile Send private message
Vithiyalan

New User


Joined: 15 Nov 2011
Posts: 6
Location: India

PostPosted: Fri Jan 20, 2012 5:04 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> HomeWorks & Requests

 


Similar Topics
Topic Forum Replies
No new posts Replace each space in cobol string wi... COBOL Programming 2
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Grouping by multiple headers DFSORT/ICETOOL 7
Search our Forums:

Back to Top