can we use max function in case statement?

Database
Enthusiast

can we use max function in case statement?

For example:

insert into table1
(amt)
select
case when t2.amt = max(t2.amt) then 0 else t2.amt end

from
Table2 t2

i'm getting an error 3504: Selected non-aggregate values should be part of the associated group.

Any suggessions please...

-Sujatha-
3 REPLIES
Enthusiast

Re: can we use max function in case statement?

the query u r tryng to run is syntacticaly wrong....try using this
SELECT
CASE
WHEN amt = COL_C THEN 0
ELSE amt
END
FROM t2,(
SELECT MAX(amt)
FROM t2) TAB_1 (COL_C).

Enthusiast

Re: can we use max function in case statement?

You can use analytics group functions :

INSERT INTO table1
( amt )
SELECT CASE
WHEN t2.amt = MAX(t2.amt)
THEN 0
ELSE t2.amt
END
FROM table2 AS t2
;
Enthusiast

Re: can we use max function in case statement?

You can use analytics group functions :

INSERT INTO table1
( amt )
SELECT CASE
WHEN t2.amt = MAX(t2.amt) OVER()
THEN 0
ELSE t2.amt
END
FROM table2 AS t2
;