|
View previous topic :: View next topic
|
| Author |
Message |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Could someone with DB2 V8 try something for me?
If you want the columns in a union to have a name, you need to give each column a correlation name. But If my memory is correct you needed to do this only in the first leg of the union. In Version 9 you have to do it in all legs
this works
| Code: |
select origin from (
select dbname, tsname as tsname, 0 as col1 , 'TB' as origin from sysibm.systables where dbname = 'DSNDB06'
union all
select dbname, name as tsname, 1 as col1 , 'TS' as origin from sysibm.systablespace where dbname like DSNDB06'
) A |
this fails
| Code: |
select origin from (
select dbname, tsname as tsname, 0 as col1 , 'TB' as origin from sysibm.systables where dbname = 'DSNDB06'
union all
select dbname, name as tsname, 1 as col1 , 'TS' from sysibm.systablespace where dbname like DSNDB06'
) A |
could someone try this second one on a DB2 V8 subsytem and tell me if it fails? thx |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
oops, just noticed I was missing a ' before my literal DSNDB06. must be some typo when replacing the dbname with one that exists everywhere.
can anyone with DB2 v8 just paste following query in SPUFI or qmf and see if it gives a -206 :
| Code: |
select col1 from (
select 'x' as col1 from sysibm.sysdummy1
union all
select 'y' from sysibm.sysdummy1
) A |
| Code: |
| COL1 IS NOT VALID IN THE CONTEXT WHERE IT IS USED. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.59.81 |
is what i get in DB2 9
the manuals of db2 are very unclear on this matter :
| V8 manual wrote: |
| The nth column of the result of UNION and UNION ALL is derived from the nth columns of R1 and R2. |
| V9 Manual wrote: |
| •If the nth column of R1 and the nth column of R2 have the same result column name, the nth column of the result table of the set operation has the same result column name. Otherwise, the nth column of the result table of the set operation is unnamed |
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
GuyC,
Got the following error message,
| Code: |
DSNT408I SQLCODE = -206, ERROR: COL1 IS NOT A COLUMN OF AN INSERTED TABLE,
UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS NOT A
COLUMN OF THE TRIGGERING TABLE OF A TRIGGER
|
Sushanth |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| ok thx |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
have you tried
SELECT A.COL1 FROM ( |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Dbz,
Same SQLCODE,
| Code: |
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT A.COL1 FROM (
SELECT 'X' AS COL1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'Y' FROM SYSIBM.SYSDUMMY1
) A
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -206, ERROR: A.COL1 IS NOT A COLUMN OF AN INSERTED TABLE,
UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS NOT A
COLUMN OF THE TRIGGERING TABLE OF A TRIGGER
DSNT418I SQLSTATE = 42703 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -600 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFDA8' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
|
Thanks,
Sushanth |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
same thing.
specifying as COL1 on both legs works:
| Code: |
select col1 from (
select 'x' as col1 from sysibm.sysdummy1
union all
select 'y' as col1 from sysibm.sysdummy1
) A |
I'm having this problem because a user has a QMF proc that is executed in batch.
with a query like this
| Code: |
select 'x1' as colxyz, 'x2' from sysibm.sysdummy1
union all
select 'y1' , 'y2' as colabc from sysibm.sysdummy1 |
and then does SAVE DATA as table2.
and a second query afterwards . The second query now fails.
The result table2 used to have columns called colxyz & colabc, now it has columns called "col1" & "col2" (Or so he claims, there's no way I can verify what he says) Well I can verify that it has col1 and col2, but not what "it used to be".
If anyone wants to execute this in QMF TSO and see what the headings in the report are, I would be grateful.
my version of qmf is :
| Quote: |
QMF HOME PANEL
Version 9 Release 1 |
I can tell the user to modify his query, but he won't be grateful.
it's a user-written query : 5 pages long with 6 unions and column names spread over the different unions. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
just for anyone having the same problem:
| IBM wrote: |
PK03946: OPTIONAL SUPPORT FOR UNION RESULT COLUMN NAME :
Prior to Version 8, the result column name in a SQLNAME field of
the SQL for a statement involving a UNION reflected the column
name or label of the first sub-query in the statement. In
Version 8, if labels are used, DB2 returns the label of the
column in the first sub-query. If labels are not used, the
result column name will only be returned if the column
is the same across all sub-queries in the statement |
as an addition: in V9 even when labels are used the
result column name will only be returned if the column
is the same across all sub-queries in the statement
Fix: opaque Zparm : UNION_COLNAME_7=YES
It is still not allowed in a subselect, but the column names coming out of describe (which QMF uses) are the column names of the first sub-query of the UNION. |
|
| Back to top |
|
|
|
|
