Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Getting problem with CURSOR implementation?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Thu Apr 17, 2008 12:08 pm    Post subject: Getting problem with CURSOR implementation?
Reply with quote

I have a table date like this,


CNTL_TBL_NME CNTL_DTE CNTL_FLAG
ARW_X9_T001 000000 E
ARW_X9_T002 000000 E
ARW_X9_T003 000000 E
ARW_X9_T004 000000 E
ARW_X9_T005 000000 E
ARW_X9_T006 000000 E
ARW_X9_T007 000000 E
ARW_X9_T008 200703 H
ARW_X9_T009 200704 H
ARW_X9_T010 200802 H
ARW_X9_T011 200803 A
ARW_X9_T012 000000 E


My requirement is i want to update the CNTL-FLAG and CNTL_DTE with cursor with the following conditions,

1.In the above table,I want to update the flag value from Where CNTL_FLAG = 'A' to CNTL_FLAG = 'H".At any ponit of time only one table is in active state,so only one row will return for always Where CNTL_FLAG = 'A'in cursor declaration.

2.After updation of CNTL_FLAG = 'A' to CNTL_FLAG = 'H" then i want to update,
a).IF the CNTL_FLAG = 'E' then i need to update CNTL_FLAG = 'A'and CNTL_DTE=CURRENt DATE
(or) IF CNTL_FLAG = 'H' Then find which is older(Minimum Date) date(CNTL_DTE) of the CNTL_FLAG = "H' then update CNTL_FLAG ='A' with CNTL_DTE=CURRENt DATE.

EXEC SQL DECLARE CNTL_TABLE_CSR CURSOR WITH HOLD FOR
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'A' OR CNTL_FLAG = 'E' OR
CNTL_FLAG = 'H' AND CNTL_DTE = (SELECT MIN(CNTL_DTE) FROM
ARW_CNTL_TABLE WHERE CNTL_FLAG = 'H')
FOR UPDATE OF CNTL_FLAG,CNTL_DTE
GROUP BY CNTL_DTE
END-EXEC.

EXEC SQL
UPDATE ARW_CNTL_TABLE
SET CNTL_FLAG = :WS-CNTL-FLAG,
CNTL_DTE = :WS-CNTL-DTE
WHERE CNTL_FLAG = :CNTL-FLAG
END-EXEC.

After Fetch it is updating all records of CNTL_FDLAG = E and CNTL_FDLAG = H to CNTL_FDLAG = A,i want only one update that too if CNTL_FDLAG = E then update with CNTL_FDLAG = A and CNTL_DTE with CURRENT DATE.IF there is no CNTL_FDLAG = E thenb only retrive only one record that is should be Minimum date of CNTL_DTE then update CNTL_FDLAG = H with CURRENT DATE.

Please let me know how to proceed further on my requirement,

The final OUTPUT should be like below

CNTL_TBL_NME CNTL_DTE CNTL_FLAG
ARW_X9_T001 000000 A
ARW_X9_T002 000000 E
ARW_X9_T003 000000 E
ARW_X9_T004 000000 E
ARW_X9_T005 000000 E
ARW_X9_T006 000000 E
ARW_X9_T007 000000 E
ARW_X9_T008 200703 H
ARW_X9_T009 200704 H
ARW_X9_T010 200802 H
ARW_X9_T011 200803 H
ARW_X9_T012 000000 E
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 17, 2008 3:40 pm    Post subject:
Reply with quote

your first requirement can be done by a normal select and update ...

Select CNTL_TBL_NME,CNTL_FLAG from table where CNTL_FLAG ='A'

as you said only one row will have CNTL_FLAG = A this will give

CNTL_TBL_NME = 'ARW_X9_T011'

now using this ARW_X9_T011 as CNTL_TBL_NAME update the flag to H

UPDATE table SET CNTL_FLAG ='H' where CNTL_TBL_NAME ='ARW_X9_T011' .

Do you require a cursor for this?

Now about your second requirement :

Quote:


a).IF the CNTL_FLAG = 'E' then i need to update CNTL_FLAG = 'A'and CNTL_DTE=CURRENt DATE



as per your data there are 7 rows with flag = 'E' now out of these which one do you want to change to 'A' ?



[/code]
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Thu Apr 17, 2008 4:17 pm    Post subject:
Reply with quote

AS Per requirement, we can update any one of the row from CNTL-FLAG='E".Because CNTL_DTE is always equal to 000000 for FLAG = E.we can update for any row,no conditions for to update flag = E rows.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 17, 2008 4:37 pm    Post subject:
Reply with quote

then do it in this sequence


SELECT CNTL_TBL_NAME INTO WS-CNTL-NAME FROM TABLE WHERE CNTL_FLAG = 'A' ;

this will give you ARW_X9_T011

UPDATE TABLE SET CNTL_FLAG = 'H' WHERE CNTL_TBL_NAME = :WS-CNTL-NAME;

SELECT CNTL_TBL_NAME INTO :WS-CNTL-NAME FROM TABLE WHERE CNTL_FLAG = 'E';

here just take care of -811 .. this select will give you the first row with CNTL_FLAG='E' ... now update this row ..


UPDATE TABLE SET CNTL_FLAG = 'A' WHERE CNTL_TBL_NAME = :WS-CNTL-NAME;

so your first row will have flag as 'A'

now if the second select returned a 100 tht means there are no rows with CNTL-FLAG as 'E' in this case do another select for the min(date) for CNTL-FLAG='H' and update accordingly ...

so instead of going for a cursor with hold you can do this with 2 selects and 2 updates ...

this is one solution ...

there might be other solutions also ... experts in this forum will provide you with other solutions if this is not feasible ...

thanks
ashimer
[/code]
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us