Accepted SQL commands? Endless calculated field syntax errors

[roattw]

I admit to not being very good with calculated fields and SQL statements.  But I have been trying to understand.  Every example reference I get I double check and learn about at:  https://www.w3schools.com/sql/

But nearly everytime I do a calculated field it hates the syntax that I got from W3Schools or a Caspio forum user.

So for Caspio specifically, is there a finite and known list of SQL commands and syntax we have to follow - so we dont waste our time with fucntions that wont work in Caspio?

And are these correct assumptions?

1. for number functions the number gets no quotes (ie WHERE field_name = 1) 
2. for text string questions you do use quotes (ie WHERE field_name = "some text") 
3. concluding semicolon not needed?  Get stuff to work sometimes with the ; sometimes or without it others
4. Is table_name reference literally just the table name (ie contacts) with no formatting
5. All references to a field must be in this format [@field:field_name]
    

I have tried some very basic forum suggested code and they always fail to verify. For example:

SELECT COUNT(ID) 
FROM table_name 
WHERE ID = target.[@field:incidentID]

or

select count(user_id) from table_name where CME_Category='Value'

What am I missing (other than basic skills ;^)

[MayMusic]

You need to make sure the spelling of the fields and table names are the same as what you have in your table. If you have a static string in WHERE clause you need to wrap it in single quote not double quotes:

WHERE field_name = "some text " Should be WHERE field_name = 'some text'

When the field name in WHERE clause is the same as then one you are comparing it to you need to use "target.". For instance:

WHERE ID = target.[@field:ID]

So if you have: WHERE ID = [@field:incidentID] since ID is not the same as incidentID you do not need to use "target."

if you are getting a value from current table source you can get it from picker and it will be in this format: [@field:FIELDNAME]

You can also receive external parameters same way you receive then on DataPages in WHERE clause: [@PARAMETER NAME]

If this parameter is string wrap it in single quote.

[Breee]

Hi Roattw, 

 

I had similiar issues when starting out. Capio's calc fields have some limitations that I wasn't accustomed to. I reached out to support and was provided with the following guidelines for the use of SQL in Caspio's calc fields: 

Here what can be used in our calculated fields:

- Any expression: https://msdn.microsoft.com/en-us/library/ms190286.aspx
- Any transact SQL function - https://msdn.microsoft.com/en-us/library/ms174318.aspx
- CASE statement - https://msdn.microsoft.com/en-us/library/ms181765.aspx
- SELECT returning scalar value: https://msdn.microsoft.com/en-us/library/ms189499.aspx

You cannot:
- Define variables 
- Define functions
- Use UPDATE/DELETE/INSERT clause.

 

Hope this helps a little. 

Bre

[JamesMobiloo]

I’m extremely interested in this thread and it is relevant to my current question.  Can you tell me where you go to create such SQL statements/commands in Caspio?  I’ve been searching of a while.  Closest I’ve got is a DataPage Report and then going to insert a calculated or aggregate field.  Am I close?

[JolliBeng]

Hi JamesMobiloo,

Yes, that is correct. You can use Calculated Fields on Report DataPages. Aside from that, you can also use SQL functions on Calculated values on Submission forms and Triggered Actions on the table level. 

You may also check the Function Reference document to see the functions that are accepted in Caspio.

~JolliBeng

[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')