problem in logic for fetching from DB2

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 ...