SQL SELECT "where" clause populated by field?

[eetimm]

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!

[Glitch]

If you're going to locate the search_alpha from the DataPage, i think you need to put target.

You can try this one:

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

[eetimm]

7 minutes ago, Glitch said:

 

Thanks for your response! I appreciate the help.

Unfortunately, it did not work.  I was not aware of the "target." designation. Can you give me a brief explanation of how that works?  

[eetimm]

22 minutes ago, Glitch said:

 

Thanks for your response! I appreciate the help.

Unfortunately, it did not work.  I was not aware of the "target." designation. Can you give me a brief explanation of how that works?  

[eetimm]

I found some info on the use of "target." here https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/.

This looks like it is getting closer. I will keep messing with it. Thanks!

 

[eetimm]

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 July 17, 2019 by eetimm
clarity

[Vitalikssssss]

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.

Regards,

Vitalikssssss

[eetimm]

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!

 

[DefinitelyNot31337]

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

 

DN31337

[DefinitelyNot31337]

*Photos for reference. Note that you may also do this in a Details DataPage.

 

[eetimm]

I will test that out. That might work for me. Thanks for the suggestion!

 

 

[victoria69]

his article was very useful. send regards for success