Using column As name in Where

[ht1815]

We're running IBM's iSeries version 5 with DB2 (I'm sorry, I don't know what version).

I'm trying to do something like the following:

SELECT CASE
WHEN CASE1 THEN 'A'
WHEN CASE2 THEN 'B'
END AS MYCASE

FROM TABLE

WHERE MYCASE > 'value'

This errors on the use of MYCASE in the Where condition. The error is to the effect that MYCASE does not exist in TABLE.

If I comment out the Where, the query will run and the column shows up with MYCASE as the heading. However, I need to be able to write a Where condition against the value of MYCASE. Is that possible? If so, what do I need to do to make it work?

[gj0104]

I would like to offer two solutions that work on the z/OS DB2 platform and are, I believe, general enough that they should work on most if not all SQL-based platforms.

1) As I understand, your query is, ostensibly, converting one value to another. Recommendation: use a series of "OR" in your predicate to equate to the "greater than" values that you are searching:

If you're looking for a "MYCASE" > 'B' then try:

SELECT CASE
WHEN CASE1 THEN 'A'
WHEN CASE2 THEN 'B'
WHEN CASE3 THEN 'C'
WHEN CASE4 THEN 'D'
END AS MYCASE

FROM TABLE

WHERE CASE3 or CASE4

=== end of example 1 ===

2) Rewrite the query using a nested-table expression (if your platform supports it).

SELECT *
FROM ( SELECT
CASE
WHEN CASE1 THEN 'A'
WHEN CASE2 THEN 'B'
WHEN CASE3 THEN 'C'
WHEN CASE4 THEN 'D'
END AS MYCASE
FROM TABLE
) AS X

WHERE X.MYCASE > 'C'

=== End of example 2 ===

Note: a) The second SELECT must be in parens
b) The "X" is required and becomes the 'new' table name


Regards,
Gary J
Colorado Springs, CO