wezzeh wezzeh - 7 months ago 16
SQL Question

Multiple case statements not working out

I'm trying to add following logic to my code (it's huge and works without this so must be something wrong with below code) but I'm getting an error. I would suspect that something is wrong with END but logically thinking all 3 should be at the end so I'm not sure

case
when t0."Final Sale Price" is null
then null
else case
when t1."Fee" is not null
and t0."Final Sale Price" >= t1."Guaranteed Price"
then t1."Fee"
else case
when t1."Fee" is not null
and t0."Final Sale Price" <= t1."Guaranteed Price"
then t1."Fee" - (t1."Guaranteed Price" - t0."Final Sale Price")
else case
when t0."Final Sale Price" < t1."Guaranteed Price"
then t0."Final Sale Price" - t1."Guaranteed Price"
else(
(
(t0."Final Sale Price" - t1."Guaranteed Price") * (100 - t1."Seller Upside %")
)
/ 100
)
end
end
end as "Actual Revenue"

vkp vkp
Answer
case 
when t0."Final Sale Price" is null then null
when t1."Fee" is not null then 
     case when t0."Final Sale Price" >= t1."Guaranteed Price" then t1."Fee" 
          when t0."Final Sale Price" < t1."Guaranteed Price" then t1."Fee" - (t1."Guaranteed Price" - t0."Final Sale Price")
     end
when t0."Final Sale Price" < t1."Guaranteed Price" then t0."Final Sale Price" - t1."Guaranteed Price"
else ((t0."Final Sale Price" - t1."Guaranteed Price") * (100 - t1."Seller Upside %")) / 100
end as "Actual Revenue"

The expression can be simplified as above.

Also note the question has 2 conditions,

t0."Final Sale Price" >= t1."Guaranteed Price"

and

t0."Final Sale Price" <= t1."Guaranteed Price"

final sale price = guaranteed price in both the conditions. If the first turns out to be true, the second condition won't be evaluated when they are equal. Make sure the = is specified only once, according to the output you expect from the case expression.

Comments