Sql statement in a case statement

[EvSam]

Hello caspio universe! 

I came across an issue and wanted to see if someone else had figured this out. I'd like to embed SQL select statements within a case statement so that a calculated field can execute a different SQL select statement based on the case conditions. In psuedo code:

Case

When [@Virtualfield1] = 'Option1' then

Select count(Column1) from tbl1 

Else

Select count(Column2) from tbl1

End

Anyone else find a way to embed a calculation (or SQL statement) within a case statement? My objective here is to run different SQL queries based on the case statement. 

Thanks for your advice!

 

[NailDyanC]

Hi @EvSam,

I believe it should work. I have the same workflow with you and I am using this formula:

CASE WHEN [@field:Company] = 'Books'
THEN
(SELECT COUNT(Order_Number) FROM Order_Info WHERE Customer_ID = [@field:Customer_ID])
ELSE
(SELECT COUNT(Phone_Number) FROM Order_Info WHERE Customer_ID = [@field:Customer_ID] AND Phone_Number != ' ')
END

 

The fields above are just representation or sample fields that I've used, you can change that depending on what field/s are you using. Also, do you encounter any error in your formula? Do you have matching fields in both tables?

[autonumber]

Hi! I agree with @NailDyanC's answer and just to add if you are using a virtual field, you may add single quote on it to avoid any errors. Just like this '[@Virtualfield1]'. Additionally, here are some links to help you use SQL statements for calculated fields.

https://www.youtube.com/watch?v=15zAruBT3LA

https://www.youtube.com/watch?v=rrutQ9i2e6o

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

[EvSam]

thanks so much for your help! I was encapsulating the statement in single quotes instead of parens and it was just driving me nuts constantly getting an error. I was worried it might be a limitation of the calculated field - thanks the sample code and direction to use parens.

[NailDyanC]

Just to add with this, you may consider as well to check these articles that might help you with some functions you can use in creating formula:

https://howto.caspio.com/function-reference/

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

[RuisiHansamu]

I don't know if this could help but here is a link from a forum post that helped me as well: 

 and  here is SQL examples that I used to learn. https://kb.iu.edu/d/ahux#:~:text=The syntax is%3A,a space between column names.

[Kurumi]

Hi! Just wanted to share this solution if you want to read or accept the single quote or apostrophe (') in the formula: https://stackoverflow.com/questions/9596652/how-to-escape-apostrophe-a-single-quote-in-mysql

Example:

Replace([@field:FIELDNAME], 'Can''t found the name', 'Remove name')