Getting problem with CURSOR implementation?

babu_hi · New User Joined: 11 Apr 2006 Posts: 93

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

ashimer · Posted: Thu Apr 17, 2008 3:40 pm

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 :

babu_hi · New User Joined: 11 Apr 2006 Posts: 93

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.

ashimer · Posted: Thu Apr 17, 2008 4:37 pm

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]