| View previous topic :: View next topic |
| Author |
Message |
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
 Posted: Tue Jan 09, 2007 2:13 am Post subject: db2 cursor query not retrieving values |
 |
|
I have a table with the following columns:
| Code: |
CREATE TABLE ABCD
(
SERIAL CHAR(06),
RUNDATE CHAR(06),
RUNNO CHAR(01),
RUNCODE CHAR(03)
) IN ABCDDB2.ABCDTS2;
INSERT INTO ABCD VALUES ('123456','07200','1','ABC');
INSERT INTO ABCD VALUES ('123456','07200','2','DEF');
INSERT INTO ABCD VALUES ('123456','07200','3','GHI');
INSERT INTO ABCD VALUES ('123456','07200','4','JKL');
INSERT INTO ABCD VALUES ('123456','07200','5','MNO');
SELECT * FROM ABCD;
---------+---------+---------+---------+---------+---------+---------+---------+
SERIAL RUNDATE RUNNO RUNCODE
---------+---------+---------+---------+---------+---------+---------+---------+
123456 07200 1 ABC
123456 07200 2 DEF
123456 07200 3 GHI
123456 07200 4 JKL
123456 07200 5 MNO
DSNE610I NUMBER OF ROWS DISPLAYED IS 5
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
|
My program code:
| Code: |
01 WS-HOST-WARS.
10 H-STRING PIC X(29) VALUE SPACES.
10 RUNC-RUN-CODE PIC X(03) VALUE SPACES.
10 H-SERIAL PIC X(06).
10 H-RUN-CODE PIC X(03).
10 H-RUN-DATE PIC X(06).
10 H-EMPL-SERIAL PIC X(06).
10 H-RUNNO PIC X(01).
01 ROWS-SWITCH PIC X(01) VALUE SPACE.
88 MORE-ROWS VALUE 'Y'.
88 NO-MORE-ROWS VALUE 'N'.
EXEC SQL DECLARE ABCD-CSR CURSOR FOR
SELECT SERIAL,RUNNO
FROM ABCD
WHERE RUNDATE = :H-RUN-DATE
AND RUNCODE IN (:H-STRING)
AND SERIAL = :H-EMPL-SERIAL
FOR FETCH ONLY
END-EXEC.
IF SQLCODE NOT EQUAL ZERO
DISPLAY 'ERROR DECLARING ABCD-CSR:SQL CODE = '
SQLCODE
PERFORM 999-ABEND-ERROR THRU 999-ABEND-ERROR-EXIT
END-IF.
MOVE '123456' TO H-EMPL-SERIAL.
MOVE '07200' TO H-RUN-DATE.
MOVE 'ABC' TO RUNC-RUN-CODE.
STRING "'" RUNC-RUN-CODE "'" DELIMITED BY SIZE
INTO H-STRING
END-STRING.
MOVE 'DEF' TO RUNC-RUN-CODE.
STRING H-STRING DELIMITED BY SPACES
",'" DELIMITED BY SIZE
RUNC-RUN-CODE DELIMITED BY SIZE
"'" DELIMITED BY SIZE
INTO H-STRING
END-STRING.
MOVE 'GHI' TO RUNC-RUN-CODE.
STRING H-STRING DELIMITED BY SPACES
",'" DELIMITED BY SIZE
RUNC-RUN-CODE DELIMITED BY SIZE
"'" DELIMITED BY SIZE
INTO H-STRING
END-STRING.
MOVE 'JKL' TO RUNC-RUN-CODE.
STRING H-STRING DELIMITED BY SPACES
",'" DELIMITED BY SIZE
RUNC-RUN-CODE DELIMITED BY SIZE
"'" DELIMITED BY SIZE
INTO H-STRING
END-STRING.
MOVE 'MNO' TO RUNC-RUN-CODE.
STRING H-STRING DELIMITED BY SPACES
",'" DELIMITED BY SIZE
RUNC-RUN-CODE DELIMITED BY SIZE
"'" DELIMITED BY SIZE
INTO H-STRING
END-STRING.
DISPLAY 'H-STRING:' H-STRING.
EXEC SQL
OPEN ABCD-CSR
END-EXEC.
IF SQLCODE NOT EQUAL TO 0
DISPLAY 'ERROR OPENING CURSOR ABCD-CSR:SQLCODE = '
SQLCODE ' RUN_DATE = ' H-RUN-DATE
' RUNCODE = ' H-RUN-CODE
' EMPL_SERIAL = ' H-EMPL-SERIAL
PERFORM 999-ABEND-ERROR THRU 999-ABEND-ERROR-EXIT
END-IF.
SET MORE-ROWS TO TRUE.
PERFORM UNTIL NO-MORE-ROWS
EXEC SQL
FETCH ABCD-CSR
INTO :H-SERIAL,:H-RUNNO
END-EXEC
EVALUATE SQLCODE
WHEN 0
DISPLAY 'SERIAL :' H-SERIAL
DISPLAY 'RUNCNO :' H-RUNNO
WHEN 100
DISPLAY 'SQLCODE:' SQLCODE
SET NO-MORE-ROWS TO TRUE
WHEN OTHER
DISPLAY 'SQLCODE:' SQLCODE
PERFORM 999-ABEND-ERROR THRU 999-ABEND-ERROR-EXIT
END-EVALUATE
END-PERFORM.
|
The output of the program is:
| Code: |
H-STRING:'ABC','DEF','GHI','JKL','MNO'
SQLCODE:000000100
|
The problem is even though there are rows matching the cursor query it is not able to fecth them leading to SQLCODE as 100.What i guess is the query being formed for the cursor is not taking the form
| Code: |
AND RUNCODE IN ('ABC','DEF','GHI','JKL','MNO') rather it is taking something else which is resulting not to get the rows from the cursor.Please let me know how can we form the query to get the correct results.
|
|
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Jan 09, 2007 6:24 am Post subject: |
|
|
1. Declare cursor sql code is returned at pre-pass not run time; you're wasting your time putting in an sql code check after the declare cursor statememt.
2. why generate all that string code? just provide values for the pic clause. would have made your example much smaller (much less code) and easier to read. not very clever to generate string statements when you know the values. you use a string statment when you have variable input and can not pre-define the values for your pic clauses.
3. since you know the value, code as: AND RUNCODE IN ('ABC','DEF','GHI','JKL','MNO')
If you want: AND RUNCODE IN (:H-STRING) - H-STRING needs to be loaded as
| Code: |
H-STRING PIC X(15) VALUE 'ABCDEFGHIJKLMNO'.
|
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Tue Jan 09, 2007 10:17 am Post subject: |
|
|
Thanks for your reply.I am actually generating the H-STRING through some other process but i am showing it the way it is getting formed and i am forming it during the program execution and i will use it in my cursor query and when i display the value of H-STRING it is
| Code: |
H-STRING:'ABC','DEF','GHI','JKL','MNO'
|
Please let me know why the cursor is not fetching values , is it because it is treating the string in some other way. |
|
| Back to top |
|
|
programmer1
Beginner
Joined: 18 Feb 2004
Posts: 138
Topics: 14
|
| Posted: Tue Jan 09, 2007 12:03 pm Post subject: |
|
|
Try including the round brackets in your string command
_________________
Regards,
Programmer |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Tue Jan 09, 2007 1:14 pm Post subject: |
|
|
| Please help me out why the cursor is not able to retreive the rows even though there are rows.How should i change my syntax because when i execute in SPUFI/QMF i am able to get the results.Thanks. |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Jan 09, 2007 4:33 pm Post subject: |
|
|
yadav2005,
your string commands are generating an IN List which looks like:
| Quote: | | 'ABC','DEF','GHI','JKL','MNO' |
which means DB2 is looking for
| Code: | 'AB
C',
'DE
F',
'GH
I',
'JK
L',
'MN
O' |
whereas, you spufi is:
| Code: | ABC
DEF
GHI
JKL
MNO
|
no, you do not need to change your syntax. You need to change how you build your IN List. As several others have also said, if you have a Working-Storage IN List (as apposed to a literal IN List - ('ABC','DEF','GHI','JKL','MNO'), you need to remove the quotes and commas. The quotes and commas in the literal IN list are required for the parse made by DB2 during the the pre-pass (pre-compiler) step so that DB2 knows that the list contains 3 char x type.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Tue Jan 09, 2007 8:58 pm Post subject: |
|
|
dbzTHEdinosauer ,
Thanks a lot for your reply and i have changed my code as below but still not able to get the results and again it is SQLCODE = 100.How to solve the problem ?
| Code: |
MOVE '123456' TO H-EMPL-SERIAL.
MOVE '07200' TO H-RUN-DATE.
MOVE 'ABC' TO RUNC-RUN-CODE.
STRING RUNC-RUN-CODE DELIMITED BY SIZE
INTO H-STRING
END-STRING.
MOVE 'DEF' TO RUNC-RUN-CODE.
STRING H-STRING DELIMITED BY SPACES
RUNC-RUN-CODE DELIMITED BY SIZE
INTO H-STRING
END-STRING.
MOVE 'GHI' TO RUNC-RUN-CODE.
STRING H-STRING DELIMITED BY SPACES
RUNC-RUN-CODE DELIMITED BY SIZE
INTO H-STRING
END-STRING.
MOVE 'JKL' TO RUNC-RUN-CODE.
STRING H-STRING DELIMITED BY SPACES
RUNC-RUN-CODE DELIMITED BY SIZE
INTO H-STRING
END-STRING.
MOVE 'MNO' TO RUNC-RUN-CODE.
STRING H-STRING DELIMITED BY SPACES
RUNC-RUN-CODE DELIMITED BY SIZE
INTO H-STRING
END-STRING.
DISPLAY 'H-STRING:' H-STRING.
|
OUTPUT
| Code: |
H-STRING:ABCDEFGHIJKLMNO
SQLCODE:000000100
|
|
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Jan 09, 2007 9:21 pm Post subject: |
|
|
take your WHERE clause out of the DECLARE CURSOR statement and try it; to insure that your RUN-UNIT connects with table ABCD in tablespace ABCDDB2.ABCDTS2
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Tue Jan 09, 2007 9:42 pm Post subject: |
|
|
Yes,
The Run Unit connects with table ABCD in tablespace ABCDDB2.ABCDTS2. |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Jan 09, 2007 9:55 pm Post subject: |
|
|
did you remove the WHERE Clause from the DECLARE CURSOR and rerun?
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Tue Jan 09, 2007 10:52 pm Post subject: |
|
|
Dick,
In my shop we are presently having a system problem for which we are unable to log on , so cannot test at this time and waiting for it.Is there any code change required to solve the problem. |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Jan 09, 2007 11:10 pm Post subject: |
|
|
at this point, I have not encountered any obvious errors. That is why I asked you to change the cursor to a 'vanilla' cursor, which will select everything in the table that you are connected to, thus proving that you are accessing the table that you loaded.
Proving your source (correct table connection) is the first basic step in checking this out. You could expand your displays to show the host variables that you have loaded that are used in the WHERE Clause - that you need to remove to insure proper connectivity.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed Jan 10, 2007 6:17 pm Post subject: |
|
|
I would also insure that the length of the IN clause object is a multiple of the length of the Column being interrogated. 29 is not a multiple of 3.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
