Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Column Correlation names in UNION

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Sep 06, 2010 8:31 pm    Post subject: Column Correlation names in UNION
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Sep 07, 2010 4:57 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 07, 2010 5:12 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Sep 07, 2010 5:20 pm    Post subject:
Reply with quote

ok thx
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 07, 2010 5:26 pm    Post subject:
Reply with quote

have you tried

SELECT A.COL1 FROM (
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 07, 2010 5:47 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Sep 07, 2010 5:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Sep 08, 2010 2:05 pm    Post subject: SOLVED
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us