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

I can't put a subquery into a coalesce in Z/OS DB2

P: n/a
I am using DB2 (version 7, soon to be updated to 8) on a mainframe that runs Z/OS.
It seems to me that the following query is legitimate, but I get an error message.
(This is not a real query; I'm just using it to experiment.)

SELECT CLASS, TYPE
FROM J3
WHERE TYPE >
COALESCE
(
(SELECT X FROM J1
WHERE W = CLASS),
'0'
);

The contents of tables J1 and J3 are shown near the end of this message.

This gives me:

QUERY MESSAGES:
SQL error at or before X (line 6, position 16).

Can anyone tell me why I am getting this message, and what I can do about it?

If I remove the coalesce -- I'm typing this one from memory, but I'm pretty sure
it's like this:

SELECT CLASS, TYPE
FROM J3
WHERE TYPE >
(SELECT X FROM J1
WHERE W = CLASS);

I don't get an error message, and I get the following output:

CLASS TYPE
----- ----
A 21
C 22

That's two-thirds of what I wanted from the first version of the query, but I also
wanted "D 23." I don't get "D 23" because, for the last row of J3, the subquery
returns a null, and <'23' is greater than a nulldoes not evaluate as true.

There is a workaround. The following query gives me what I wanted from the first
query:

SELECT CLASS, TYPE
FROM J3
WHERE TYPE >
(SELECT X FROM J1
WHERE W = CLASS)
OR NOT EXISTS
(SELECT X FROM J1
WHERE W = CLASS)
;

The result is:

CLASS TYPE
----- ----
A 21
C 22
D 23

Maybe this type of workaround would always work, so I would never have to put a
subquery in a coalesce. But I'm not sure; maybe there are some situations I can't
think of now that wouldn't lend themselves to this sort of workaround. Even if it
would always work, I would still like to know why my original query gets an error
message.

Here are the tables:

J1:

W X
- --
A 11
B 12
C 13
J3:

CLASS TYPE
----- ----
A 11
A 21
B 12
C 13
C 22
D 23

Any help would be appreciated.

Dan Amodeo
Oct 20 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Use a LEFT OUTER JOIN and place the COALESCE into the SELECT list.
One more reason to get to DB2 V8 for zOS :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 20 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.