SQLCODE -203

No Name Available

SQLCODE -203
Hello,

I am having a problem with the below query at BIND time. I am trying to
minimize coding, and save extra work when we go to V8, by using the group
items in the FETCH:

FETCH RI_PARENT INTO
:DCL-RLIBM :DCL-RLIBM-NI
, :DCL-FKIBM :DCL-FKIBM-NI
, :DCL-RLWRK :DCL-RLWRK-NI
, :DCL-FKWRK :DCL-FKWRK-NI

I guess my question, can the parser not figure out something as simple as
this, or is there another way around this?

DECLARE RI_PARENT CURSOR FOR
SELECT *
FROM (SELECT *
FROM SYSIBM.SYSRELS RL
, SYSIBM.SYSFOREIGNKEYS FK
WHERE FK.CREATOR = 'COLLA'
AND FK.TBNAME = 'TABLE001'
AND FK.CREATOR = RL.CREATOR
AND RL.RELNAME = FK.RELNAME ) BASE
FULL OUTER JOIN
(SELECT *
FROM SYSIBM.SYSRELS RL
, SYSIBM.SYSFOREIGNKEYS FK
WHERE FK.CREATOR = 'COLLB'
AND FK.TBNAME = 'TABLE001'
AND FK.CREATOR = RL.CREATOR
AND RL.RELNAME = FK.RELNAME ) TARG
ON BASE.RELNAME = TARG.RELNAME
AND BASE.COLNAME = TARG.COLNAME
ORDER BY BASE.RELNAME, BASE.COLSEQ

DSNT408I SQLCODE = -203, ERROR: A REFERENCE TO COLUMN RELNAME IS AMBIGUOUS
DSNT418I SQLSTATE = 42702 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -525 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFDF3' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tony Provenzola

Re: SQLCODE -203
(in response to No Name Available)
The problem is, using the "SELECT *" in the nested table expression, you get duplicate column names. The Column RELNAME is ambiguous in your 'ON' clause because there are 2 BASE.RELNAME columns and 2 TARG.RELNAME columns. The SQL manual refers to this:

"If a nested table expression is specified, the result table is the result of that nested table expression. The columns of the result do not need unique names, but a column with a non-unique name cannot be referenced."

The only way around it that I know of is to specify the columns and give them unique names (RL.RELNAME AS RL_RELNAME).

Tony Provenzola
Nike Database Services
Venturi Technology Partners, Consulting
Phone * 503-532-0772
Fax * 503-532-3223
Email * [login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: Tuesday, January 13, 2004 1:11 PM
To: [login to unmask email]
Subject: SQLCODE -203


Hello,

I am having a problem with the below query at BIND time. I am trying to
minimize coding, and save extra work when we go to V8, by using the group
items in the FETCH:

FETCH RI_PARENT INTO
:DCL-RLIBM :DCL-RLIBM-NI
, :DCL-FKIBM :DCL-FKIBM-NI
, :DCL-RLWRK :DCL-RLWRK-NI
, :DCL-FKWRK :DCL-FKWRK-NI

I guess my question, can the parser not figure out something as simple as
this, or is there another way around this?

DECLARE RI_PARENT CURSOR FOR
SELECT *
FROM (SELECT *
FROM SYSIBM.SYSRELS RL
, SYSIBM.SYSFOREIGNKEYS FK
WHERE FK.CREATOR = 'COLLA'
AND FK.TBNAME = 'TABLE001'
AND FK.CREATOR = RL.CREATOR
AND RL.RELNAME = FK.RELNAME ) BASE
FULL OUTER JOIN
(SELECT *
FROM SYSIBM.SYSRELS RL
, SYSIBM.SYSFOREIGNKEYS FK
WHERE FK.CREATOR = 'COLLB'
AND FK.TBNAME = 'TABLE001'
AND FK.CREATOR = RL.CREATOR
AND RL.RELNAME = FK.RELNAME ) TARG
ON BASE.RELNAME = TARG.RELNAME
AND BASE.COLNAME = TARG.COLNAME
ORDER BY BASE.RELNAME, BASE.COLSEQ

DSNT408I SQLCODE = -203, ERROR: A REFERENCE TO COLUMN RELNAME IS AMBIGUOUS
DSNT418I SQLSTATE = 42702 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -525 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFDF3' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm