Jump to content
  • 0

Sql statement in a case statement


EvSam
 Share

Question

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!

 

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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?

Link to comment
Share on other sites

  • 0

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/

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...