SQL CASE SELECT statement with conditions

[Ras2019]

Hi,

I am trying to get data from a table to a datapage calculated field based on conditions that are chosen in the data page.. I get to work without any CASE & WHEN conditions i.e. if I only have to SELECT from one column, but that only takes me halfway.

Caspio continue to give me the ..... Incorrect syntax near (.   and I have tried to rewrite many times.

I would appreciate it a lot if anyone can see what im doing wrong in below statement!

 

CASE

WHEN [@field:Agree]="NO" AND [@field:Term]="Disc1" 

THEN

SELECT Price FROM Products WHERE Model=target.[@field:Model] AND Color=target.[@field:Color] 

WHEN [@field:Agree]="YES" AND [@field:Term]="Disc1" 

THEN

SELECT PriceReb FROM Products WHERE Model=target.[@field:Model] AND Color=target.[@field:Color] 

END

 

Sincerely

Ras

[Vitalikssssss]

Hi @Ras2019,

You should wrap your SQL query with brackets and also use "0/1" for checkbox field comparison.

So, your formula should look like this:

CASE

WHEN [@field:Agree]=0 AND [@field:Term]="Disc1" 

THEN

(SELECT Price FROM Products WHERE Model=target.[@field:Model] AND Color=target.[@field:Color]) 

WHEN [@field:Agree]=1 AND [@field:Term]="Disc1" 

THEN

(SELECT PriceReb FROM Products WHERE Model=target.[@field:Model] AND Color=target.[@field:Color]) 

END

Hope this helps.

Regards,

vitalikssssss

[Ras2019]

Hi,

Thanks a lot Vitalikssssss.

Works perfect now, even with using dropdown yes/no.

Sincerely

Ras

 

[ianGPT]

Hi,

Just wanted to share this article that will be helpful for calculated fields and formulas https://howto.caspio.com/function-reference/