| View previous topic :: View next topic |
| Author |
Message |
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
 Posted: Tue May 08, 2007 11:05 am Post subject: unable to make use of a Cursor in a program |
 |
|
Hai All,
I am trying to make use of a Cursor in my program which is as follow:
| Code: |
SELECT EMPNO,EMPNAME
FROM EMPLOYEE
WHERE GENDER = 'M'
AND JOIN_DATE >= :DB2-FROM-DATE
AND JOIN_DATE <= :DB2-TO-DATE
ORDER BY EMPNO
FOR FETCH ONLY
|
I get the DB2-FROM-DATE and DB2-TO-DATE after reading a file and the record structure is in the format:
| Code: |
01 DATE-REC.
COPY DATEREC.
COPY BOOK DATEREC HAS THE FOLLOWING DECLARATION
05 DB2-FROM-DATE PIC X(10).
05 DB2-TO-DATE PIC X(10).
|
But when i try to compile the code i am getting error as:
| Code: |
IGYPS2160-S "ADDRESS OF" operand "DB2-FROM-DATE" was found as the sending operand of a "SET" statement, but was not a "LINKAGE SECTION", "WORKING-STORAGE SECTION" or "LOCAL-STORAGE SECTION" item, or was a level-66 or level-88 item. The statement was discarded.
IGYPS2160-S "ADDRESS OF" operand "DB2-TO-DATE" was found as the sending operand of a "SET" statement, but was not a "LINKAGE SECTION", "WORKING-STORAGE SECTION" or "LOCAL-STORAGE SECTION" item, or was a level-66 or level-88 item. The statement was discarded.
|
Can anybody help me how to solve the problem ? |
|
| Back to top |
|
 |
CICS Guy
Intermediate
Joined: 30 Apr 2007
Posts: 292
Topics: 3
|
| Posted: Tue May 08, 2007 11:17 am Post subject: |
|
|
If the copybook is still in the file section, then it is not "a "LINKAGE SECTION", "WORKING-STORAGE SECTION" or "LOCAL-STORAGE SECTION" item"....
Move date-rec to date-ws.
01 DATE-WS.
COPY DATEREC
:DB2-FROM-DATE of date-ws |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue May 08, 2007 11:42 am Post subject: |
|
|
another reason for READ with work-area option.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
CICS Guy
Intermediate
Joined: 30 Apr 2007
Posts: 292
Topics: 3
|
| Posted: Tue May 08, 2007 12:46 pm Post subject: |
|
|
| dbzTHEdinosauer wrote: | | another reason for READ with work-area option. |
Isn't that called "Cross Posting"?  |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue May 08, 2007 1:48 pm Post subject: |
|
|
| CICS Guy wrote: | | dbzTHEdinosauer wrote: | | another reason for READ with work-area option. |
Isn't that called "Cross Posting"? |
is that a no-no?
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Wed May 09, 2007 1:20 am Post subject: |
|
|
Thanks CICS Guy and dbzTHEdinosauer,
I was able to fix the problem based upon your suggestions and now again i am getting SQL CODE = +012 while opening the cursor.And based on the manuals i am unable to find what is the problem in the query:
[code:1:02f9b70283]
+012 THE UNQUALIFIED COLUMN NAME
column-name WAS INTERPRETED AS A
CORRELATED REFERENCE
Explanation: The column name does not identify a
column of a table or view in the FROM clause of the
subquery. However, it does identify a column of a table
or view in a FROM clause at a higher level in the
statement.
System Action: The column name is interpreted as a
correlated reference.
Programmer Response: If DB2 |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed May 09, 2007 1:27 am Post subject: |
|
|
yadav2005,
any chance we can see the 'column-name' in the sql msg:
| Quote: | +012 THE UNQUALIFIED COLUMN NAME
column-name WAS INTERPRETED AS A
CORRELATED REFERENCE |
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Wed May 09, 2007 3:09 am Post subject: |
|
|
Thanks Dick,
I am using the query as below:
| Code: |
******************************************************************
* MESSAGE STRUCTURES *
******************************************************************
01 DSNTIAR-MSG.
05 DSNTIAR-MSG-LENGTH PIC S9(4) COMP VALUE +960.
05 DSNTIAR-MSG-TEXT OCCURS 8 TIMES
INDEXED BY DSNTIAR-INDEX
PIC X(120).
01 DSNTIAR-LINE-LENGTH PIC S9(9) COMP VALUE +120.
EXEC SQL DECLARE 1-CSR CURSOR FOR
SELECT EMPNO,EMPNAME
FROM EMPLOYEE
WHERE GENDER = 'M'
AND JOIN_DATE >= :DB2-FROM-DATE
AND JOIN_DATE <= :DB2-TO-DATE
ORDER BY EMPNO
FOR FETCH ONLY
END-EXEC.
IF SQLCODE NOT EQUAL ZERO
DISPLAY 'ERROR DECLARING CURSOR 1-CSR:'
' SQL CODE = ' SQLCODE
PERFORM ABEND-ERROR
END-IF.
I am calling DB2 Standard Error Routine in my program
ABEND-ERROR.
DISPLAY 'ABEND ERROR'.
IF SQLCODE NOT EQUAL ZERO
CALL 'DSNTIAR' USING SQLCA DSNTIAR-MSG
DSNTIAR-LINE-LENGTH
IF RETURN-CODE EQUAL ZERO
DISPLAY '***BEGIN-SQL-ERROR***'
PERFORM WITH TEST AFTER
VARYING DSNTIAR-INDEX FROM 1 BY 1
UNTIL DSNTIAR-INDEX GREATER THAN 8
IF DSNTIAR-MSG-TEXT(DSNTIAR-INDEX) NOT EQUAL SPACE
DISPLAY DSNTIAR-MSG-TEXT(DSNTIAR-INDEX)
END-IF
END-PERFORM
DISPLAY '***END-SQL-ERROR*****'
END-IF
END-IF.
MOVE 12 TO RETURN-CODE.
PERFORM STOP-RUN.
|
When i run my program i am getting only info as :
| Code: |
ERROR DECLARING CURSOR 1-CSR: SQL CODE = 0000000012
ABEDND ERROR
|
I do not know how to display the column name , can u guide me ahead ? |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed May 09, 2007 4:22 am Post subject: |
|
|
ok, a few things.
1. have you ever noticed that DECLAREs do not generate any inline code? After all, they can be placed in WORKING-STORAGE - how do you check the SQLCODE then?
there is no need to check the SQLCODE after a DECLARE CURSOR. if you do, set the SQLCODE (actually INITIALIZE the SQLCA before you do anything........... Remove this check after DECLAREs
| Code: | IF SQLCODE NOT EQUAL ZERO
DISPLAY 'ERROR DECLARING CURSOR 1-CSR:'
' SQL CODE = ' SQLCODE
PERFORM ABEND-ERROR
END-IF.
|
that is popping up because you have not INITIALIZED the SQLCODE before you checked it.
2. This is redundent code. you would not be here unless the SQLCODE <> 0, I suggest that you remove it
| Code: |
DISPLAY 'ABEND ERROR'.
IF SQLCODE NOT EQUAL ZERO
|
3. I would not check the RETURNCODE after the call to DSNTIAR. Spaces in the msg text are your key......
4. really does not matter, but I code my working storage for the DSNTIAR CALL this way:
| Code: |
01 ERROR-MESSAGE.
05 ERROR-LEN PIC S9(4) BINARY VALUE +1320.
05 ERROR-TEXT PIC X(132)
OCCURS 10 TIMES
INDEXED BY AUSWERT-IDX.
88 NICHTS-DRIN VALUE SPACES.
01 ERROR-TEXT-LEN PIC S9(9) BINARY VALUE +132.
01 PGM-CONSTANTS.
05 IBM-MODUL-DSNTIAR PIC X(08) VALUE 'DSNTIAR'.
|
my CALL and display are:
| Code: |
CALL IBM-MODUL-DSNTIAR <<<<I always use dynamic linking
USING
SQLCA
ERROR-MESSAGE
ERROR-TEXT-LEN
END-CALL
PERFORM VARYING AUSWERT-IDX
FROM 1
BY 1
UNTIL AUSWERT-IDX
> 10
IF NICHTS-DRIN
IN ERROR-TEXT
IN ERROR-MESSAGE
(AUSWERT-IDX)
THEN
CONTINUE
ELSE
DISPLAY ERROR-TEXT
IN ERROR-MESSAGE
(AUSWERT-IDX)
END-IF
END-PERFORM
|
as additional info, I capture the SQLERRMC when there is a msg (SQLERRML > 0)
| Code: |
IF SQLERRML > ZERO
THEN
MOVE SQLERRMC ( 1:SQLERRML)
TO WS-SQLERRMC
ELSE
|
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Thu May 10, 2007 5:43 am Post subject: |
|
|
Thanks Dick,
I initialized SQLCA and i was able to fix the problem and my program is working fine now.Thank you. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
