case and MAX

ahmedwaseem2000 · #1 (**permalink**) 05-25-07, 08:20

what is the wrong with this????

Code:

MAX (CASE WHEN MAX (X1)=val1 THEN X2
ELSE WHEN MAX (Y1)=val2 THEN Y2) AS XXXX  )

baloo99 · #2 (**permalink**) 05-25-07, 08:32

several tips:
-- missing END
-- SHIP-VIOL-D-Q - instead '-' use '_'
instead max(x1) use subselect

or paste whole select

stolze · #3 (**permalink**) 05-25-07, 13:30

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:

Code:

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
               ELSE NULL
            END
     FROM   t1 )
SELECT MAX(val)
FROM   t2

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech