problem in logic for fetching from DB2

enrico-sorichetti · Posted: Sun Jul 13, 2008 12:06 pm

maybe I overlooked something,
from theposted rows and the code snippets it does not look like You need a cursor
You are getting field1,fielsd2,field3 from somewhere else

so just do a select with a condition on field1 field2 field3 and field6
to get all the other values

if OK
delete the 'INVALID'
insert the 'CURRENT'

but as I said maybe I overlooked something

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

enrico-sorichetti · Posted: Sun Jul 13, 2008 12:20 pm

but You did not show it in the sample rows

You showed only

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

what i now plan to do to get around with this is fetch the cursor resultset into a table, and then loop separately to insert the same into the database.. i think that will work.. but i am not very good at cobol tables.

is the below fine? pls help me.

DECLARATION:
01 TABLE-2.
02 INDVDL-TAB OCCURS 1 TO 20 TIMES
DEPENDING ON WS-COUNT
INDEXED BY WS-INDEX.
03 FIELD4 PIC S9(9) USAGE COMP.
03 FIELD5 PIC S9(11)V USAGE COMP-3.

I WILL COUNT THE NUMBER OF RECORDS THAT ARE PRESENT IN THE TABLE WITH FIELD1, FIELD2, FIELD3 WITH FIELD6 AS 'INVALID' AND MOVE IT INTO WS-COUNT.
FETCHING:

SET WS-INDEX TO 1
PERFORM FETCH-INDVDL-CURSOR
THRU FETCH-EXIT UNTIL WS-INDEX > WS-COUNT
OR SQLCODE-CHK = +100.

AND THEN WITHIN THE PARA:

*********************
FETCH-INDVDL-CURSOR.
*********************
SET WS-INDEX UP BY 1
EXEC SQL
FETCH INDVDL
INTO :INDVDL-TAB
END-EXEC.
MOVE SQLCODE TO SQLCODE-CHK.
IF SQLCODE = 0
CONTINUE
ELSE
MOVE SQLCODE OF SQLCA TO SQL-CODE
PERFORM 9999-ERROR-ROUTINE-PARA
THRU 9999-EXIT
MOVE WS-ERR-DISPLAY TO SQL-ERROR-DESC
END-IF.

ADD 1 TO WS-ROW

FETCH-EXIT.
EXIT.

WHILE INSERTING:
PERFORM INSERT-INTO-TABLE UNTIL WS-INDEX > WS-ROW

*****************
INSERT-INTO-TABLE.
*****************
MOVE FIELD1 (WS-INDEX) TO VAL1
MOVE FIELD2 (WS-INDEX) TO VAL2

EXEC SQL
INSERT INTO XYZ
VALUES
(
:WS-IN1,
:WS-IN2,
:WS-IN3,
:VAL1,
:VAL2,
'CURRENT'
)
END-EXEC.
IF SQLCODE = 0
CONTINUE
ELSE
MOVE SQLCODE OF SQLCA TO SQL-CODE
PERFORM 9999-ERROR-ROUTINE-PARA
THRU 9999-EXIT
MOVE WS-ERR-DISPLAY TO SQL-ERROR-DESC
END-IF.

enrico-sorichetti · Posted: Sun Jul 13, 2008 12:36 pm

please please, watch Your typing,
You make very difficult for people trying to help to follo w Your line of thought

You started by speaking about &duplicating the rows with the "obsolete" string,

now in the last post You speak about the rows with invalid....

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

oops.. sorry for the typo.. once again to clarify:

i need to delete 'INVALID' rows
i need to insert 'CURRENT' rows
for rows that have 'OBSOLET'

rag swain · Posted: Sun Jul 13, 2008 9:01 pm

your cursor declaration statement contains FIELD4 and FIELD5 whereas you are fetching the cursor into :VAL1 only.

Suresh Ponnusamy · Posted: Sun Jul 13, 2008 10:52 pm

Hi darakhshan

Your example and Cursor declaration is confusing

TABLE XYZ before operations:

AAA BBB CCC 1 10 OBSOLET
AAA BBB CCC 2 20 OBSOLET
AAA BBB CCC 3 30 OBSOLET
AAA BBB CCC 3 30 INVALID

Here you have 'OBSOLET' as 6th field.

But your cursor declaration is

EXEC SQL
DECLARE INDVDL CURSOR WITH HOLD FOR
SELECT
FIELD4,
FIELD5
FROM
XYZ
WHERE
FIELD1 = :WS-IN1
AND FIELD2 = :WS-IN2
AND FIELD3 = :WS-IN3
WITH UR
END-EXEC.

Your delete is as follows

DELETE-FROM-TABLE.

EXEC SQL
DELETE FROM XYZ
WHERE
FIELD5 = 'OBSOLET'
AND FIELD1 = :WS-IN1
AND FIELD2 = :WS-IN2
AND FIELD3 = :WS-IN3
END-EXEC.

But you have mentioned that you want to delete records with the value as "INVALID"

And your insert statement

EXEC SQL
INSERT INTO XYZ
VALUES
(
:WS-IN1,
:WS-IN2,
:WS-IN3,
:VAL1,
:VAL2,
'CURRENT' )
END-EXEC.

You are inserting the "CURRENT" again as 6th field.

Could you please verify the above once again and correct if something was mistaken.

vicky11121982 · New User Joined: 21 Jun 2007 Posts: 9 Location: gurgaon

Try this n check

MOVE FILE VARIABLES TO WS-VARIABLES(WS-FIELD1,WS-FIELD2,WS-FIELD3)

DECLARE CUR1 FOR
SELECT * FROM XYZ
WHERE FEILD1 = :WS-FIELD1
AND FEILD2 = :WS-FIELD2
AND FEILD3 = :WS-FIELD3

MAIN-PARA
SET END-OF CSR1 TO FALSE
PERFORM FETCH-CSR UNTIL END-OF CSR1
READ NEXT RECORD FROM FILE

FETCH-CSR
FETCH CUR1
INTO :WS1-FIELD1,
:WS1-FIELD2,
:WS1-FIELD3,
:WS1-FIELD4,
:WS1-FIELD5,
:WS1-FIELD6
IF SQL 0
IF WS-FIELD6 = 'INVALID'
PERFORM DEL-PARA
PERFORM INSERT-PARA
ELSE
PERFORM INSERT-PARA
IF SQL 100
SET END-OF CSR1 TO TRUE

INSERT-PARA
INSERT INTO XYZ
VALUES(WS-FIELD1,
WS-FIELD2,
WS-FIELD3,
WS1-FIELD4,
WS1-FIELD5,
'CURRENT')

DEL-PARA
DELETE THE RECORD WITH INVALID

ashimer · Posted: Mon Jul 14, 2008 3:01 pm

Darakshan, i suggest asking your seniors to review your program once ... this will be solved in no time .... Pls Dont hesitate ....

dbzTHEdinosauer · Posted: Mon Jul 14, 2008 3:04 pm

ashimer,

why do you think this post is 2 pages. The OP seems to be reluctant to ask his peers about this problem.

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

hello all,
thank u very much for all ur help..
the problem was resolved on using cobol tables..

enrico-sorichetti · Posted: Mon Jul 14, 2008 7:56 pm

You found a workaround, not a solution

ashimer · Posted: Mon Jul 14, 2008 8:27 pm

Did you find out wht was the prob with the prev code ???

If not keep a copy of tht code with you and whn u get free time, try to figure it out ... and pls let us know ...