MVSFORUMS.com

[Susila]

Hi,
I am trying to embed a query which uses DECLARE GLOBAL TEMPORARY TABLE in COBOL but I am getting the following error message
STATEMENT REFERENCES UNDECLARED TABLE "RELATED_PROGRAMS"

My query
EXEC SQL
DECLARE GLOBAL TEMPORARY TABLE RELATED_PROGRAMS
(SOURCE_ID INTEGER, TARGET_ID INTEGER, ROUND SMALLINT)
ON COMMIT PRESERVE ROWS
END-EXEC.

EXEC SQL
INSERT INTO SESSION.RELATED_PROGRAMS
INSERT INTO RELATED_PROGRAMS
SELECT DISTINCT 0, P.ENT_ID, 1
FROM DBXIMS.DBX_IMS_PROGRAM P,
DBXREL30.DBX_XREF X
WHERE
X.ENT_TYPE = 151 AND
P.PGM_NAME = 'ADR0910P'
END-EXEC.

I tried the same query in SPUFI it works fine.
Please give your valuable suggestion in this regard.

Thanks in advance....

[Susila]

There a mistake in the SQL.PLease find the correct SQL below.

EXEC SQL
DECLARE GLOBAL TEMPORARY TABLE RELATED_PROGRAMS
(SOURCE_ID INTEGER, TARGET_ID INTEGER, ROUND SMALLINT)
ON COMMIT PRESERVE ROWS
END-EXEC.
EXEC SQL
INSERT INTO SESSION.RELATED_PROGRAMS
SELECT DISTINCT 0, P.ENT_ID, 1
FROM DBXIMS.DBX_IMS_PROGRAM P,
DBXREL30.DBX_XREF X
WHERE
X.ENT_TYPE = 151 AND
P.PGM_NAME = 'ADR0910P'
END-EXEC.

[Susila]

DSNH204I W DSNHANAL LINE 350 COL 17 STATEMENT REFERENCES UNDECLARED TABLE "SESSION"."RELATED_PROGRAMS"
INSERT INTO SESSION.RELATED_PROGRAMS SELECT DISTINCT 0,P.ENT_ID,1 FROM DBXIMS.DBX_IMS_PROGRAM P,DBXREL30.DBX_XREF X
WHERE X.ENT_TYPE=151 AND P.PGM_NAME='ADR0910P'

I am getting this same error in other referring (Declare global temporary table reference) statement also.

I tried to display the SQLCODE along with message but I couldn

[kolusu]

Susila,

Are you gettting that error while compiling your program? If that is the case then make sure that your sql statements start after col 12

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[kolusu]

Ravi,

If you notice carefully , the messages are from PRE-COMPILATION step. The program did not even compile properly. So your logic of displaying the sql code won't even come into picture.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[Susila]

Hi,

Ya I am getting the compilation error.
I checked out my SQL statements it starts after 12th column only.

Susila.

[kolusu]

Susila,

If you look at your error messages, it shows that DSNH204I W DSNHANAL LINE 350 COL 17 STATEMENT REFERENCES UNDECLARED TABLE "SESSION"."RELATED_PROGRAMS".

If your sql statement starts on/after 12 your table name will be around 24th col. But looking at your messages, your sql statements are starting before col 12. Did you have NUM ON STD COB in your profile?

TYPE 'NUM ON STD COB' at the command prompt without the single quotes and press ENTER.

Now check your sql statements and make sure that they are starting on/after col 12

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu