By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,276 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,276 IT Pros & Developers. It's quick & easy.

UNION ALL not returning expected results

P: n/a
TC
select 'y' value from sysibm.sysdummy1 where 1=1
union
select 'x' value from sysibm.sysdummy1 where 1=2

Why is it that this will not return 'y'. It seem to return nothing
unless both sides of this union have a result.

Aug 8 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
TC wrote:
select 'y' value from sysibm.sysdummy1 where 1=1
union
select 'x' value from sysibm.sysdummy1 where 1=2

Why is it that this will not return 'y'. It seem to return nothing
unless both sides of this union have a result.
It works for me:
$ db2 -t
db2 =select 'y' value from sysibm.sysdummy1 where 1=1
union
select 'x' value from sysibm.sysdummy1 where 1=2 db2 (cont.) =db2
(cont.) =;

VALUE
-----
y

1 record(s) selected.

My database server is running DB2 V8.2 FP10 on AIX.

Aug 8 '06 #2

P: n/a
Tom:

It probably shouldn't matter, but what platform are you on? We're on
LUW 8.2.3 (AIX), and I was able to run your statement:

select 'y' from sysibm.sysdummy1 where 1=1
union
select 'x' from sysibm.sysdummy1 where 1=2;
------------------------------------------------------------------------------
select 'y' from sysibm.sysdummy1 where 1=1 union select 'x' from
sysibm.sysdummy1 where 1=2

1
-
y

1 record(s) selected.

Note that you mention UNION ALL in the Subj. but your example just uses
UNION. Are you getting any error/warning messages? Also, notice how in
the above example, only one value--'y'--is returned, whereas in the
following example two "values" are returned: a NULL and a 'Y'. How many
"values" (I use the scare quotes because NULL isn't really a value) are
you expecting?

SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT NULLIF('A','A') FROM
SYSIBM.SYSDUMMY1;
------------------------------------------------------------------------------
SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT NULLIF('A','A') FROM
SYSIBM.SYSDUMMY1

1
-
-
Y

2 record(s) selected.

--Jeff

TC wrote:
select 'y' value from sysibm.sysdummy1 where 1=1
union
select 'x' value from sysibm.sysdummy1 where 1=2

Why is it that this will not return 'y'. It seem to return nothing
unless both sides of this union have a result.
Aug 8 '06 #3

P: n/a
IMHO defaulting UNION to be DISTINCT was one of the early blunders of
SQL. My guess is that the majority of UNIONs out there should be UNION
ALL.*sigh*

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 9 '06 #4

P: n/a
TC
Thanks all for trying this on your platforms.

We are running UDB 8.2 FP 12 (with special release for crash issue we
had) on AIX 5.3. This union seems to have these results with any
"values" view.

You are correct that this was meant to be a union all. I had just
pasted an iteration from my testing where I tried to change it to a
union to see if that had any affect.

Aug 9 '06 #5

P: n/a
TC wrote:
Thanks all for trying this on your platforms.

We are running UDB 8.2 FP 12 (with special release for crash issue we
had) on AIX 5.3. This union seems to have these results with any
"values" view.

You are correct that this was meant to be a union all. I had just
pasted an iteration from my testing where I tried to change it to a
union to see if that had any affect.
Seeing is believing... can you post the actual statement with the actual
output from the actual shell?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 9 '06 #6

P: n/a
TC
db2 =connect to d611a3 user d611a3 using d611a3

Database Connection Information

Database server = DB2/AIX64 8.2.5
SQL authorization ID = D611A3
Local database alias = D611A3

db2 =select 'x' from sysibm.sysdummy1 where 1=1 union all select 'y'
from sysibm.sysdummy1 where 1=2

1
-

0 record(s) selected.

We have been able to reproduce this with a vanilla FP 12 on windows as
well. I have started a PMR for the issue.

Aug 9 '06 #7

P: n/a
TC wrote:
db2 =connect to d611a3 user d611a3 using d611a3

Database Connection Information

Database server = DB2/AIX64 8.2.5
SQL authorization ID = D611A3
Local database alias = D611A3

db2 =select 'x' from sysibm.sysdummy1 where 1=1 union all select 'y'
from sysibm.sysdummy1 where 1=2

1
-

0 record(s) selected.

We have been able to reproduce this with a vanilla FP 12 on windows as
well. I have started a PMR for the issue.
Forgive me for asking a stupid question please.

Does "select 'x' from sysibm.sysdummy1 where 1=1" by itself return the
record?

B.

Aug 9 '06 #8

P: n/a
TC
Does "select 'x' from sysibm.sysdummy1 where 1=1" by itself return the
record?
Not a dumb question -- Yes, it does. On our AIX FP12 special build we
can reproduce this through control center or command line. We can
reproduce this on our Windows FP 12 environment only though our
application using bind variables -- Not through control center. (I
know, that makes it even more strange)

Aug 9 '06 #9

P: n/a
TC wrote:
>Does "select 'x' from sysibm.sysdummy1 where 1=1" by itself return the
record?

Not a dumb question -- Yes, it does. On our AIX FP12 special build we
can reproduce this through control center or command line. We can
reproduce this on our Windows FP 12 environment only though our
application using bind variables -- Not through control center. (I
know, that makes it even more strange)
It works fine for me on Linux and a stock DB2 V8.2.5.

I think you should open a PMR with IBM support.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 10 '06 #10

P: n/a

TC wrote:
Does "select 'x' from sysibm.sysdummy1 where 1=1" by itself return the
record?

Not a dumb question -- Yes, it does. On our AIX FP12 special build we
can reproduce this through control center or command line. We can
reproduce this on our Windows FP 12 environment only though our
application using bind variables -- Not through control center. (I
know, that makes it even more strange)
Hmm.. good luck then! :)

B.

Aug 10 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.