Forum > Databases

IBX filterRecords and calcFields

(1/2) > >>

Nicole:
A IBQuery populates a DBStingGrid.
This Query is filtered by OnFilterRecords and does as it should.
The data-fields are fine.

Next step:

After the filter there are some more fields which shall be calculated.
They behave strange.
On debugging it seems to be, that the already filtered rows are passed on for calculation.

I checked the manual.
There I found, I can set "SQL filter" to true and this is just for "tables".
?!
I am clueless about the details, and what I tried did not work.

And the bad news:
Filtering by SQL-statment right from the DB source will not be possible. The IBquery is a join-request filling half a page. It is too complex.
What does this property IBQuery "SQL filtered" mean in detail?
There is a hint in the manual about "server side filters", which does not say too much to me.
Can I use it at all?
and: I use a local server.

Or in other words:
What can I do, that the IBquery CalcFields just work with my already "FilteredRecords"?



rvk:

--- Quote from: Nicole on September 16, 2022, 06:40:33 pm ---After the filter there are some more fields which shall be calculated.
They behave strange.
On debugging it seems to be, that the already filtered rows are passed on for calculation.
--- End quote ---
Why would this be strange?
I can image you may want to filter on calculated values. So they first need to be calculated.


--- Quote from: Nicole on September 16, 2022, 06:40:33 pm ---And the bad news:
Filtering by SQL-statment right from the DB source will not be possible. The IBquery is a join-request filling half a page. It is too complex.
--- End quote ---
Maybe the filtered SQL can be simplified.
(You might be amazed by how many SQL's can be simplified, if not direct in SQL then by stored procedure.)

BTW it's always preferred to not filter on client side. Because otherwise the complete table would need to be transferred to client-side to be filtered. Filtering server-side is much more efficient.


--- Quote from: Nicole on September 16, 2022, 06:40:33 pm ---What does this property IBQuery "SQL filtered" mean in detail?
There is a hint in the manual about "server side filters", which does not say too much to me.

--- End quote ---
Server side filters (SQLFiltered) are just for dynamically adding WHERE to the SQL clause.
You can also do that manually in code yourself.


--- Quote from: Nicole on September 16, 2022, 06:40:33 pm ---What can I do, that the IBquery CalcFields just work with my already "FilteredRecords"?

--- End quote ---
You could check in CalcField if the record is filtered, and if so, just skip the calculation.

But why is this a problem?
And why is using WHERE or even SQLFiltered a problem?

Nicole:
Why I want to filter first and calculate after it?
Because it is quicker to calculate a bundle of figures instead of all of them.

If you say this is solvable, may be for you.
I am really poor at group and union or so. There SHOULD be a way, but I will not find it.
I tried, but failed.

The problem is this:
There a generated sorted groups of "comm". From each of this group (mostly 5) I would need the first 3 each 'comm'. The trick is that "first 3" may destroy the complex sort, which is the most important thing in the result.

This is the statement


--- Code: MySQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---select fk_jdatum, o, h, l, c, volume, openinterest,fk_trade, fk_kontrakt, id_kurszeilen,tbkontrakte.id_kontrakt, tbkontrakte.name_ as name_, tbkontrakte.comm as comm, tbkontrakte.jahr_aus_name as jahrfrom tbkurszeilen JOIN tbkontrakte on fk_kontrakt = tbkontrakte.id_kontrakt where scrolled is null and fk_jdatum = '03.08.2022'order by comm, jahr asc, name_ asc, openinterest desc  
I attach you a result sheet where you can see what I mean.

rvk:

--- Quote from: Nicole on September 16, 2022, 08:07:43 pm ---Why I want to filter first and calculate after it?
Because it is quicker to calculate a bundle of figures instead of all of them.
...
There a generated sorted groups of "comm".
--- End quote ---
What is the calculated field?
If COMM is the calculated field, it is absolutely needed that it is calculated for every record when filtering (because the filtering depends on it).

If the calculated field is on another field, how much time is the calculation really?
(fields are already in memory so calculation shouldn't take too long.)

But it might be preferable, as is the same with filtering, to do calculation on server side. It depends on the 'calculation'.

The query you want (limiting records to 3 per COMM) should be doable in Firebird.
But it is tricky. And if it can't be achieved with a SUB SELECT (select from (select)), it can always be done with an EXECUTE BLOCK.

In firebird 3.0+ (and MySql 8+) it's much easier because there is a ROW_NUMBER () OVER <window-specification>.
In that case you can just add the ROW_NUMBER per COMM change and limit it by doing a "row_number < 4".
An fiddle example in Firebird 3.0 is here: https://dbfiddle.uk/UZ4mNLbh (for MySQL it's here: https://dbfiddle.uk/RnvwyIZN)
The double sub-select was needed because in the select itself row_number isn't know yet.
You can play (fiddle) with it on that page.

(the Firebird version even has the possibility to do the ordering in the "over (partition)" part.)

In Firebird 2.5- it a lot trickier and needs to be done by SUB SELECTs or EXECUTE BLOCKs.
(although I advised against it earlier, for this feature it might be worth to upgrade, at least to test it out  :D)

Nicole:
Thank you for the answer.
"Calculated" does not work yet, so you do cannot see a calculated field on the screenshot.

If the calculation field is performance consuming? Oh, yes. It is a complex date-situation, which calculates events for certain week days and working days etc. All the stuff, they kept from Babylon and which does not work decimal. I can feel the difference on scrolling because it takes nearly a second.

"comm" is just a field on my table, not calculated.
If tell you what comm means, you will understand at sudden:
Comm stands for commodity. So if you want to check, if your next heating period will be affordable anymore, you check for the rows where comm = "HO" (=heating oil). If you think "within a year can happen much", you will filter all rows of the year 2024 and spring 2023. This is the reason, why I need just three of "comm", but of every "comm".

Well, about Firebird 3. Yes, I will take it. But not now. You cannot upgrade all at the same time. Then you will never get out of the mess.

My Delphi had a solution with OnCalcFields and Filtering which works fine. Lazarus shall do this as well sooner or later.

To work on:
What does this property SQL-filter mean?
And is there a way to stop calculating and filtering for scrolling? Both just shall be done on the click event of one button. This button makes the software reading the (changed) filter-criteria new.

Or is there a way to DELETE rows of myquery in the filter event?

Navigation

[0] Message Index

[#] Next page

Go to full version