Union Problem

[atefshehata]

hi all,

i'm new to db2 and i have a simple question ..
Using db2 ver 7.
i have a table named 'Locations' with three varchar columns
LocId,LocDesc,LocType

when i execute >
select * from Locations union all select LocId,LocDesc,LocType from
Locations
it causes an error

and when i execute >
select * from Locations union all select * from Locations
it works.

i know you asking yourself why this mad man write this union.

actually this is a situation i asked to explain from one of my friends
...

any help please
thanks

atef

[Serge Rielau]

at*********@hotmail.com wrote:

hi all,

i'm new to db2 and i have a simple question ..
Using db2 ver 7.
i have a table named 'Locations' with three varchar columns
LocId,LocDesc,LocType

when i execute >
select * from Locations union all select LocId,LocDesc,LocType from
Locations
it causes an error

and when i execute >
select * from Locations union all select * from Locations
it works.

i know you asking yourself why this mad man write this union.

actually this is a situation i asked to explain from one of my friends
..

any help please
thanks

atef

Atef,

What's the exact error message?
The error condition which I deem to be most likley would be that the
columns are defined in a different order than you think.
So the second UNION ALL branches column order does not match the first.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[atefshehata]

Serge

thanks for your reply

What's the exact error message? i'm using oledb driver to connect , and it produces a strange error
(internal error 3119) !!!
The error condition which I deem to be most likley would be that the
columns are defined in a different order than you think.
So the second UNION ALL branches column order does not match the first.

I'm sure the columns order is correct
and from my knowledge in other DBMS (SQL , Sybase) the order of columns
is not an issue if we use a column name not a constants

example)
if table1 contains 3 columns c1,c2,c3 .

select * from table1 union all select c1,c2,c3 from table1
should works even if the order is not matched

do you think aliases could be the reason !?

as i said before i'm new to db2 (just started from 3 days)..

thanks again ,waiting for any help

Atef

[Mark A]

<at*********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I'm sure the columns order is correct
and from my knowledge in other DBMS (SQL , Sybase) the order of columns
is not an issue if we use a column name not a constants

example)
if table1 contains 3 columns c1,c2,c3 .

select * from table1 union all select c1,c2,c3 from table1
should works even if the order is not matched

do you think aliases could be the reason !?

as i said before i'm new to db2 (just started from 3 days)..

thanks again ,waiting for any help

Atef

In DB2 the columns in both sides of a UNION must have the same length and
data type. That means the first column must match the first column, the
second column must match the second column, and so forth. So it is always
best to specifically list the column names on all parts of a UNION. Using
Select * should be avoided for anything but casual adhoc use.

[Serge Rielau]

at*********@hotmail.com wrote:

Serge

thanks for your reply

What's the exact error message?

i'm using oledb driver to connect , and it produces a strange error
(internal error 3119) !!!

The error condition which I deem to be most likley would be that the
columns are defined in a different order than you think.
So the second UNION ALL branches column order does not match the first.

I'm sure the columns order is correct
and from my knowledge in other DBMS (SQL , Sybase) the order of columns
is not an issue if we use a column name not a constants

example)
if table1 contains 3 columns c1,c2,c3 .

select * from table1 union all select c1,c2,c3 from table1
should works even if the order is not matched

do you think aliases could be the reason !?

as i said before i'm new to db2 (just started from 3 days)..

thanks again ,waiting for any help

Atef

Well, first order of buisness is to get the OLEDB out of the way as cause.
Can you connect using the command line processor (CLP)?
Then type the statement from there as we see where we get.
My earlier comment was geared towards questioning whether the table is
truly defined as (c1, c2, c3) and not e.g. (c2, c1, c3).

I would still like to see the error message, weird or not....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[bones]

I know it sounds strange but I have encountered similar problems in the
past. Instead of writing:

select * from x UNION ALL select c1, c2, c3 from x

try:

select c1, c2, c3 from x UNION ALL select * from x

Sometimes, specifying individual columns in the first part of the quey
helps over specifying "*".

HTH

David

[Serge Rielau]

bones wrote:

I know it sounds strange but I have encountered similar problems in the
past. Instead of writing:

select * from x UNION ALL select c1, c2, c3 from x

try:

select c1, c2, c3 from x UNION ALL select * from x

Sometimes, specifying individual columns in the first part of the quey
helps over specifying "*".

HTH

David

Alright folks.. show me.
I'd like to see a repro script :-)
Also I next the exact version of DB2 and platform (zOS, LUW).
DB2 for zOS supports a ROWID column.
I wonder whether there is confusion whether it shows on '*'.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab