View previous topic :: View next topic
|
Author |
Message |
rohit_naidu
New User
Joined: 18 May 2018 Posts: 3 Location: UK
|
|
|
|
Hi,
Am facing a Sql error -204(Object not Found) when trying to open a cursor which has Temporary table reference. Have done below analysis on this issue.
1) Validated Temporaray table creation and records insertion was successfully by displaying and fetching the values from Temp Table.
2) Ensured that Temporary Table was created before opening the cursor
3) Table and Fields name are correct.
4) If replaces Temporary table with hard code values, cursor is working fine.
Below is the Cursor and Temporary table definition followed with open cursor in the sequence executed in the Program
Code: |
WORKING STORAGE SECTION.
EXEC SQL
DECLARE CJLR-OUT-NOTICE-CSR CURSOR FOR
SELECT DISTINCT
PARTNO
FROM PDDB2DB.PWRSFUP
WHERE PROD_TYPE||VEHICLE_LINE IN
(SELECT DISTINCT WWRS_VEHICLE FROM SESSION.VEHICLE
WHERE WWRS_VEH_REQ = "CJLR")
END-EXEC.
PROCEDURE DIVISION.
EXEC SQL
DECLARE GLOBAL TEMPORARY TABLE SESSION.VEHICLE
(WWRS_VEHICLE CHAR(3) NOT NULL,
WWRS_VEH_REQ CHAR(4) NOT NULL)
ON COMMIT PRESERVE ROWS
END-EXEC
EXEC SQL
INSERT INTO SESSION.VEHICLE
(WWRS_VEHICLE,WWRS_VEH_REQ)
VALUES (:WS-VEHICLE,:WS-VEH-REQ)
END-EXEC
EXEC SQL
OPEN CJLR-OUT-NOTICE-CSR
END-EXEC |
Please adviSe what is causing this issue and how to resolve it. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
What are the pictures for the fields? Have you displayed them between markers e.g.
? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
It looks like the temp table is getting Dropped during the process before you are opening the cursor.
Try DROPing the temp table before its Declaration and get rid of the DROP from elsewhere( if present).
After opening the cursor what next? are you processing it in the same program like the FETCH? |
|
Back to top |
|
|
rohit_naidu
New User
Joined: 18 May 2018 Posts: 3 Location: UK
|
|
|
|
Nic Clouston wrote: |
What are the pictures for the fields? Have you displayed them between markers e.g.
? |
Its alphanumeric fields.
WWRS_VEHICLE PIC X(03).
WWRS_VEH_REQ PIC X(04). |
|
Back to top |
|
|
rohit_naidu
New User
Joined: 18 May 2018 Posts: 3 Location: UK
|
|
|
|
Rohit Umarjikar wrote: |
It looks like the temp table is getting Dropped during the process before you are opening the cursor.
==> Have displayed the Value from this temp table before opening the cursor to check if Temp table exist or not. Its displaying required value.
Try DROPing the temp table before its Declaration and get rid of the DROP from elsewhere( if present).
==> It will throw the SQL error, haven't tried yet. Do u want me to try
After opening the cursor what next? are you processing it in the same program like the FETCH? |
==> fetching the data and writing into output file |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Try the other thing suggested of Dropping the temp table if nothing works out then check with the DBA at your site. |
|
Back to top |
|
|
|