loadRecords() With Max() data?

Classic Servoy
1

Hi Folks
I need to show a form with loadRecords() and have the SQL return functions of the query. So there are several columns that need to be MAX, and grouped by another. Here is the SQL I’m looking at :

var newSQL = "SELECT "+
	"corrosion_condition.cc_area, "+
	"Max(corrosion_condition.cc_short_corr_rate), "+
	"Max(corrosion_condition.cc_long_corr_rate), "+
	"Max(corrosion_condition.cc_av_loss) "+
	"FROM corrosion_condition "+
	"WHERE corrosion_condition.cc_short_corr_rate <>  'NULL' "+
	"GROUP BY corrosion_condition.cc_area ";

I realise loadRecords() needs a pk so is there a way to actually run this and show the grouped data in the form - without creating a temp table and showing that? What is best practice in this case?

FOLLOW-UP
I added cc_id and looks like using this code:

var dataset = databaseManager.getDataSetByQuery('tci_test',newSQL,null,-1);

forms.dashboard_frm_corro_analysis_sub_area.controller.loadRecords(dataset)

We get the dataset into the foundset for the form as desired!

Cheers :D

2

Hi Ian,

I think your query should then look like this:

var newSQL = "SELECT cc_id "+
   "FROM corrosion_condition "+
   "WHERE corrosion_condition.cc_short_corr_rate <>  'NULL' "+
   "GROUP BY corrosion_condition.cc_area ";

No need to put the functions in the query when you don’t need it for loading the foundset.

Hope this helps.

3

ROCLASI:
Hi Ian,

I think your query should then look like this:

var newSQL = "SELECT cc_id "+

"FROM corrosion_condition "+
"WHERE corrosion_condition.cc_short_corr_rate <> ‘NULL’ "+
"GROUP BY corrosion_condition.cc_area ";



No need to put the functions in the query when you don't need it for loading the foundset.

Hope this helps.

Thanks Robert - but that obviously misses the point of the MAX functions in the query - or did I miss your point??

Using the dataset in the load records fixes what we need - good learning for us for sure.

4

Hi Ian,

When you load a dataset into the form using controller.LoadRecords() then it only uses the given PK. I.e. the first column of your dataset.
All the rest is ignored.

So to show the max of these fields you need to setup your own aggregates objects to use as dataproviders on the form.

5

ROCLASI:
Hi Ian,

When you load a dataset into the form using controller.LoadRecords() then it only uses the given PK. I.e. the first column of your dataset.
All the rest is ignored.

So to show the max of these fields you need to setup your own aggregates objects to use as dataproviders on the form.

I was fooled because there are so few test records in this column.

However - if that’s the case Robert - what’s the best practice to be able to do a MAX or any other function in SQL and have that part of the Foundset?

Is it necessary to build a table to hold the calculated records then do a pk_id query against that?

6

Hi Ian,

If you want to use a query with aggregates in it and load it as a foundset then you need to use the solution model and use the dataset as a datasource for the form. Then all the columns (aggregate functions and all) will be dataproviders which you can attach to a field/label.

The question is if you want it to be that dynamic.
You can simply create those MAX() aggregates in Servoy (see aggregate tab in the table editor) and use those as dataproviders on your form. Just load the proper foundset (like with the query I posted) and you have the same result.

7

ROCLASI:
Hi Ian,

If you want to use a query with aggregates in it and load it as a foundset then you need to use the solution model and use the dataset as a datasource for the form. Then all the columns (aggregate functions and all) will be dataproviders which you can attach to a field/label.

The question is if you want it to be that dynamic.
You can simply create those MAX() aggregates in Servoy (see aggregate tab in the table editor) and use those as dataproviders on your form. Just load the proper foundset (like with the query I posted) and you have the same result.

Lots of work for what should be a simple task don’t you think! Worked around it anyway for the moment.

Cheers

Robert