There are several semantical and syntactical problems:
- Have a look at the syntax diagram for CASE expressions, first. The construct "ELSE WHEN" is not right at all.
- What should the result be if the equality comparisons are both not met or just one is not met?
- What is the nested MAX(X1) and MAX(Y1) supposed to be doing? They need a table and a column to operate on. Both compute the maximum of all the values in column X1 and Y1, respectively. Then you throw things off because a CASE expression operates on a single row only - not a table.
Maybe you want to do this:
I think this is much clearer. The rest is the optimizer's job to do something useful with it and come up with the best access plan.
WITH t1(max_x1, max_y1) AS
( SELECT MAX(x1), MAX(y1)
FROM ... ),
t2 (val) AS
( SELECT CASE
WHEN max_x1 = val1 THEN x2
WHEN max_y1 = val2 THEN y2
FROM t1 )
IBM DB2 Analytics Accelerator
IBM Germany Research & Development