Case Statements to IF

[Dataguy46]

Hello,

I have  been trying to convert Case to IF and i am getting syntax error,

 thanks in advance for looking at this.

:

 (CASE WHEN `productTypeName`='DisplayBase' THEN (CASE WHEN `Lead Count` IS NOT NULL THEN `Lead Count` ELSE `Lead Volume` END)*`orderCostPerUnit`/1000 ELSE (CASE WHEN `Lead Count` IS NOT NULL THEN `Lead Count` ELSE `Lead Volume` END)*`orderCostPerUnit` END) AS 'Total Order Value'

[RafaelBarrios · Accepted solution]

Hi @Dataguy46 

try

Load
.....,
.....,
if(productTypeName='DisplayBase',
    (if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)/1000,
    (if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)
) as [Total Order Value],
....
FROM <where ever>

 or, a bit nicer

Load
.....,
.....,
(if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)/
  if(productTypeName='DisplayBase',1000,1) as [Total Order Value],

....
FROM <where ever>

It may have some syntax error because I don't have anywhere to test them right now, but I hope you get the idea.

field name with white spaces or especial characters needs to have [ ] or " "

hope this helps

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!