Jump to content
  • 0
roattw

Accepted SQL commands? Endless calculated field syntax errors

Question

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 ;^)

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×