# Thread: case and MAX

1. Registered User
Join Date
Jan 2005
Posts
80

## Unanswered: case and MAX

what is the wrong with this????

Code:
```MAX (CASE WHEN MAX (X1)=val1 THEN X2
ELSE WHEN MAX (Y1)=val2 THEN Y2) AS XXXX  )```
Last edited by ahmedwaseem2000; 05-25-07 at 09:30.

2. Registered User
Join Date
Feb 2007
Location
Bratislava, Slovakia
Posts
85
several tips:
-- missing END
-- SHIP-VIOL-D-Q - instead '-' use '_'
instead max(x1) use subselect

or paste whole select

3. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•