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

DB2/390 v8 - union all subquery in select list returns wrong results

P: n/a
All,

Seeing the behaviour below on DB2 v8 on zOS
create table test(intcol integer);
insert into test values (1);
insert into test values (2);
insert into test values (3);
insert into test values (4);
insert into test values (5);

SELECT
(SELECT 'BELOW' FROM SYSIBM.SYSDUMMY1 WHERE INTCOL <= 2
UNION ALL
SELECT 'ABOVE' FROM SYSIBM.SYSDUMMY1 WHERE INTCOL > 2)
FROM TEST

This returns

BELOW
BELOW
<null>
<null>
<null>

Which is ...unexpected. Replacing UNION ALL with UNION seems to
workaround the issue, but that is not a valid solution for my
environment.

Anyone any thoughts?

Thanks,

Peter

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Can you use CASE expression as workaround?

SELECT
CASE
WHEN INTCOL <= 2 THEN 'BELOW'
WHEN INTCOL > 2 THEN 'ABOVE'
END
FROM TEST

Nov 12 '05 #2

P: n/a
Yes that should work. Thanks. Does look like a DB2 bug though, don't
you (all) think so.
Peter

Nov 12 '05 #3

P: n/a
pg*********@pandora.be wrote:
Yes that should work. Thanks. Does look like a DB2 bug though, don't
you (all) think so.
Peter

Yes. Please contact support. This one shouldn't be dropped on the floor.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
you could also try:

select 'BELOW' from test where intcol <=2
union all
select 'ABOVE' from test where intcol > 2

I believe that the case solution is a better one than this.
Philip Sherman

pg*********@pandora.be wrote:
All,

Seeing the behaviour below on DB2 v8 on zOS
create table test(intcol integer);
insert into test values (1);
insert into test values (2);
insert into test values (3);
insert into test values (4);
insert into test values (5);

SELECT
(SELECT 'BELOW' FROM SYSIBM.SYSDUMMY1 WHERE INTCOL <= 2
UNION ALL
SELECT 'ABOVE' FROM SYSIBM.SYSDUMMY1 WHERE INTCOL > 2)
FROM TEST

This returns

BELOW
BELOW
<null>
<null>
<null>

Which is ...unexpected. Replacing UNION ALL with UNION seems to
workaround the issue, but that is not a valid solution for my
environment.

Anyone any thoughts?

Thanks,

Peter

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.