Link to home
Create AccountLog in
Avatar of Sathish David  Kumar N
Sathish David Kumar NFlag for India

asked on

is it possible to use query inside the DECODE()

is it possible to use query inside the DECODE()

SELECT MAX(DECODE(grp,  'n1', (select max(n1) from xxx a,xxx b a.grp=b.grp)
                           ,  'n2', n2,  'n3', n3,  'n4', n4))
              FROM xxx b
             
like this
Avatar of Qlemo
Qlemo
Flag of Germany image

Yes it is, but your subquery is wrong, it would be:
SELECT MAX(DECODE(grp,  'n1', (select max(n1) from xxx a a.grp=b.grp)
                           ,  'n2', n2,  'n3', n3,  'n4', n4))
   FROM xxx b

Open in new window

I recommend to use this instead, as using a subquery in the select list can perform bad:
SELECT MAX(DECODE(grp,  'n1', maxn1,  'n2', n2,  'n3', n3,  'n4', n4))
  FROM xxx b left join (select grp, max(n1) as maxn1 from xxx group by grp) maxn
     on maxn.grp = b.grp

Open in new window

I suppose the query is constructed, as a max(max(...)) doesn't make much sense, in particular if applied to the same table xxx. Since the max(n1) part is only used if grp = 'n1', we could restrict each subquery to exactly that: where grp = 'n1'.
ASKER CERTIFIED SOLUTION
Avatar of ikamil
ikamil
Flag of Russian Federation image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer