Jump to content
  • 0

SQL SELECT "where" clause populated by field?



I am trying to use a SQL query in a calculated field to calculate the max value in a sub-group of data.  When I hard code the formula as below, it works fine.

     SELECT Max(CY_Inc) FROM Pohl_Data_Temp
     WHERE cert = '8426' OR cert='7506' OR cert='660' OR cert='11681' OR cert='29679' OR cert='9507'

What I am trying to do is fill in my where statement dynamically from a field in my database. When I use the following formula, it returns a blank result.

     SELECT Max(CY_Inc) FROM Pohl_Data_Temp 
     WHERE cert = [@field:search_alpha]

     Field [search_alpha] =  '8426' OR cert='7506' OR cert='660' OR cert='11681' OR cert='29679' OR cert='9507'
     Field [cert] is a unique ID field

Since the hard coded formula works, I believe that I have the syntax correct, leading me to conclude that a SQL Select in a calculated field cannot use a field to provide the conditions for the where statement.

I have not used a lot of SQL in calculated fields, so it is possible that I am missing something here.

I appreciate any thoughts and insights!

Link to comment
Share on other sites

11 answers to this question

Recommended Posts

  • 0

I am going to document my efforts here just in case anyone else is looking for a similar answer...

When I use the "target.[@field:search_alpha] with a single entry in the field (using '8426' rather than the string "'8426' or cert='7506'"), I do get the correct response. So, it appears the issue is calculating the max value from the multiple records I am submitting.  It appears that the SQL needs a match of a single data point in the WHERE clause.

Any other methods to grab the max value out of a range of values in a calculated field, using another field as the criteria? 

Edited by eetimm
Link to comment
Share on other sites

  • 0

Hi @eetimm,

Please note that below syntax in SQL query is incorrect because it is treated as a single string value:

18 hours ago, eetimm said:

"'8426' or cert='7506'"

How do you store data in the field "search_alpha"? 

It would help if you could provide us with more insights on how this field is generated and what is the desired output of the Trigger.



Link to comment
Share on other sites

  • 0

Thanks for the help.

The data in the search_alpha field is text, and I am using a multi-select listbox to create the field, and then using JS to format the field as "8426 OR 7506...". I use this field to create a subset of my overall database by passing the string as a parameter and using "contains" to limit the data.

In this particular case, I am hoping to do some calculations (Min, Max, Avg) on the subset.  My approach was to see if I could pass the string to the WHERE clause of a SQL Select statement so I can make the calculation only on the values in the subset.  Not sure if this is the correct approach.

The overall project is an online analysis tool that allows users to select a subset of industry firms and then view some basic metrics. There will be multiple users running the tool at the same time, and each one will be able to save their own subset of the industry for analysis.

I appreciate any help!


Link to comment
Share on other sites

  • 0

Hello eetimm,


If I may suggest, maybe you can generate your results the Caspio way in a DataPage.

1.) Create a Tabular Report with Pohl_Data_Temp as your DataSource

2.) For "Configure Search Page Fields", set the Form Element of cert field to ListBox, then on Advanced, you can allow Multi-Select.

3.) To get max(CY_Inc), just sort your results with this field in descending order.

4.*) Optional. To get top 1, you can limit your results to just 1.


Hope this helps



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.

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.

  • Create New...