CODEWITHSUNDEEP
sqlcaseamazon-redshift
mei
mei

Reputation: 35

Syntax error for nested condtionals in SQL

I'm pretty new to redshift and I've been trying to to do a nested case when condition here, yet I get a syntax error

ERROR: syntax error at or near ")"

in this line of SQL:

ELSE ROUND((last_bid * positions), 2))

I'm not sure what's wrong, since I'm familiar with Python and I'm pretty sure this is how nested conditions work

SELECT 
    *, 
    CASE
       WHEN asset_type = 'EQUITY' 
          THEN (CASE
                   WHEN positions < 0 THEN ROUND((positions * last_ask), 2)
                   ELSE ROUND((last_bid * positions), 2))
          ELSE ROUND((positions * last_ask/100), 2)
    END AS MARKET_VALUE
FROM 
    base_report

Upvotes: 0

Views: 64

Answers (3)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Please try this:

SELECT *

     , CASE
        WHEN asset_type ='EQUITY' THEN 
        (CASE
        WHEN positions < 0 THEN ROUND((positions * last_ask),2)
        ELSE ROUND((last_bid * positions),2) end)
        
        ELSE ROUND((positions * last_ask/100),2)
      END  as MARKET_VALUE

FROM base_report

Upvotes: 0

Data Monger
Data Monger

Reputation: 106

SELECT *,
        
    CASE
    WHEN asset_type = 'EQUITY' THEN
    (
    CASE
    WHEN positions < 0 THEN
    ROUND((positions * last_ask), 2)
    ELSE ROUND((last_bid * positions), 2)
    END )
    ELSE ROUND((positions * last_ask / 100), 2)
    END AS MARKET_VALUE
FROM base_report

Please do let me know if this works

Upvotes: 1

christie.li
christie.li

Reputation: 46

You should end the nested CASE, like that:

ELSE ROUND((last_bid * positions), 2)
END
)

Upvotes: 2

Related Questions