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

1 answer 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

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


×