Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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: 1281
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: 1281
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: 6968
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: 1281
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: 1281
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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us