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

4 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
  • 0

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?

Share this post


Link to post
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...

×
×
  • Create New...