View previous topic :: View next topic |
Author |
Message |
omswarup Beginner
Joined: 12 Dec 2006 Posts: 6 Topics: 2 Location: Bangalore
|
Posted: Thu Mar 19, 2009 3:31 am Post subject: Cursor handling in Easytrieve |
|
|
i am stuck with a weird problem in eastrieve. Could you please help me out in this.
I have a eaasytrieve program which uses a input file to get a particular value like policy number and fetches the data from the table to get a field from it and writes a ouput which is same as that of the input but the field obtained from the table is also written in the record.
I am using cursors to do it, but the problem is for every read i need to open the cursor, fetch the record and close it , if i open the cursor using the start proc and close the cursor using the finish proc it doesnot fetch the values from the table and is giving SQl code as 100. But that record is there in the table.
Please help. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Thu Mar 19, 2009 10:32 am Post subject: |
|
|
omswarup,
You have opened the same question in 2 forums and you sent me a private message and an email?
You need to understand what START/FINISH proc does.
START proc is executed only ONCE at the beginning of the program
FINISH proc is executed only ONCE just before the termination of the program
You need to open and close the cursor for every record you read from the file
Ideally this is how your program should be. This is untested program just from memory
Code: |
FILE INFILE
LOOK-UP-KEY 10 05 A
FILE-POLICY-NUM 50 10 A
SQL DECLARE C1 CURSOR FOR +
SELECT POLICY_NUM +
FROM table +
WHERE col1 = :WS-FILE-VAL
WS-FILE-VAL W 05 A
WS-TABLE-VAL W 10 A
*
JOB INPUT INFILE
WS-FILE-VAL = ' '
WS-TABLE-VAL = ' '
WS-FILE-VAL = LOOK-UP-KEY
SQL OPEN C1
IF SQLCODE = 0
SQL FETCH C1 INTO :WS-TABLE-VAL
IF SQLCODE NE 0 100
DISPLAY ' SQLCODE >>>> ' SQLCODE
DISPLAY ' ERROR FETCHING CURSOR C1 '
STOP EXECUTE
END-IF
ELSE
DISPLAY ' SQLCODE >>>> ' SQLCODE
DISPLAY ' ERROR OPENING CURSOR C1 '
STOP EXECUTE
END-IF
SQL CLOSE C1
IF SQLCODE NE 0
DISPLAY ' SQLCODE >>>> ' SQLCODE
DISPLAY ' ERROR CLOSING CURSOR C1 '
STOP EXECUTE
END-IF
IF WS-TABLE-VAL NE FILE-POLICY-NUM
PUT OUTPUT
END-IF
|
Kolusu |
|
Back to top |
|
|
omswarup Beginner
Joined: 12 Dec 2006 Posts: 6 Topics: 2 Location: Bangalore
|
Posted: Thu Mar 19, 2009 11:33 pm Post subject: |
|
|
Thanks a lot Kolusu for the reply , Apologies for posting the same question in two forums.
I had one more clarification , cant we open the cursor only once and then fetch the table for all the policy numbers in the input and close it at the end.
Is it very necessary to open and close the cursor for every fetch? |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Fri Mar 20, 2009 10:17 am Post subject: |
|
|
omswarup wrote: | Thanks a lot Kolusu for the reply , Apologies for posting the same question in two forums.
I had one more clarification , cant we open the cursor only once and then fetch the table for all the policy numbers in the input and close it at the end.
Is it very necessary to open and close the cursor for every fetch? |
omswarup,
yes you can , however you need to code your own logic for matching with the file value. psuedo code
1. Open the cursor with an ORDER by on the policy num without any where clause
2. Now read your file (it also needs to be sorted on the compare key)
3. if file key equal db2 key value write output
4. If file key less than db2 key value read the file
5. If file key greater than db2 key value fetch the cursor till you get to the File key value
Ideally I would unload the db2 table and do the file match using easytrieve which would be much simpler and efficient |
|
Back to top |
|
|
omswarup Beginner
Joined: 12 Dec 2006 Posts: 6 Topics: 2 Location: Bangalore
|
Posted: Mon Mar 23, 2009 4:27 am Post subject: |
|
|
kolusu wrote: | omswarup wrote: | Thanks a lot Kolusu for the reply , Apologies for posting the same question in two forums.
I had one more clarification , cant we open the cursor only once and then fetch the table for all the policy numbers in the input and close it at the end.
Is it very necessary to open and close the cursor for every fetch? |
omswarup,
yes you can , however you need to code your own logic for matching with the file value. psuedo code
1. Open the cursor with an ORDER by on the policy num without any where clause
2. Now read your file (it also needs to be sorted on the compare key)
3. if file key equal db2 key value write output
4. If file key less than db2 key value read the file
5. If file key greater than db2 key value fetch the cursor till you get to the File key value
Ideally I would unload the db2 table and do the file match using easytrieve which would be much simpler and efficient |
Thank you Kolusu.... i have taken this approach now its working absolutely fine.... You are a real inspiration for all of us... Hats off... Thanks a ton |
|
Back to top |
|
|
|
|