CODEWITHSUNDEEP
sqloracle-database
Lara
Lara

Reputation: 3021

How to use subquery inside DECODE in Oracle

I am trying to use a sub-query inside and Oracle Decode statement as shown below

RPAD(NVL(DECODE(TRIM(ST.StudentCode),'AB','CA','TM','CH',(Select InternalNumber from Address where State = SA.STATECODE) <=2,'PAS', ST.StudentCode), ' '), 3, ' ')

when I am running this part with my original query I am getting error as "Missing right paranthesis" in the same line. What is being wrong here?

Upvotes: 0

Views: 3335

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Just use a single case expression:

RPAD( (CASE WHEN TRIM(ST.StudentCode) = 'AB' THEN 'CA',
            WHEN TRIM(ST.StudentCode) = 'TM' THEN 'CH',
            WHEN (Select a.InternalNumber from Address a where a.State = SA.STATECODE) <= 2 THEN 'PAS'
            ELSE COALESCE(ST.StudentCode, ' '), 
       ), 3, ' ')

Upvotes: 2

Ori Marko
Ori Marko

Reputation: 58772

You can add case expression inside your sub query:

RPAD(NVL(DECODE(TRIM(ST.StudentCode),'AB','CA','TM','CH',CASE WHEN (Select InternalNumber from Address where State = SA.STATECODE) <=2 THEN 'PAS' ELSE ST.StudentCode END), ' '), 3, ' ')

Oracle Database searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.

Upvotes: 1

Related Questions