Recent

Author Topic: Firebird: Order by on join.  (Read 5297 times)

iru

  • Sr. Member
  • ****
  • Posts: 321
Firebird: Order by on join.
« on: October 27, 2021, 10:21:41 am »
Environment: Linux Mint 20.2, Laz 2.0.12, FPC 3.2.2, FB 3.0

Gentlefolk, I have the following SQL on an IBQuery. Works well, reads table and all records are accessible in the IBQuery for table EventData.

Select PROGRAM_ID, PROGRAM_EVENT From PROGRAMDATA
inner join EVENTDATA on
PROGRAMDATA.Program_ID = EventData.Event_NO

I wish to sort the records on EventData.Event_NO.
I have tried appending various versions of

   order by EventData.EVENT_NO asc; 

With NO success. Test ok in the SQL editor but records do not get sorted.

Any ides, suggestions appreciated, Ian.

Zvoni

  • Hero Member
  • *****
  • Posts: 2316
Re: Firebird: Order by on join.
« Reply #1 on: October 27, 2021, 11:13:13 am »
Have you tried sorting by PROGRAMDATA.Program_ID? Should give the same (expected) order since you use an INNER JOIN

EDIT: I consider it bad practice to Sort by a not selected column, since at first look the order could appear to be arbitrary.
Even if in your case the sort-order should be the same, since you're joining on program_id=event_no
« Last Edit: October 27, 2021, 11:34:45 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: Firebird: Order by on join.
« Reply #2 on: October 27, 2021, 11:33:18 am »
Zvoni,

Thanks for the rapid response.

I tried 'order by ProgramData.Program_ID desc;' (and asc).

Checks OK in the SQL editor but no success, records are still out of order.

Ian

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: Firebird: Order by on join.
« Reply #3 on: October 27, 2021, 11:52:50 am »
You must select the field to be sorted in the Select clause too, even if you don't display it:

Code: MySQL  [Select][+][-]
  1. SELECT PROGRAM_ID, PROGRAM_EVENT, EVENT_NO
  2. FROM PROGRAMDATA
  3.     INNER JOIN EVENTDATA ON PROGRAMDATA.Program_ID = EVENTDATA.Event_NO
  4. ORDER BY EVENT_NO ASC;
« Last Edit: October 27, 2021, 12:00:21 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

Zvoni

  • Hero Member
  • *****
  • Posts: 2316
Re: Firebird: Order by on join.
« Reply #4 on: October 27, 2021, 12:00:40 pm »
Eric, since when?
I was under the impression as long as there is no UNION or GROUP BY-Clause you can sort by a not selected column
https://www.wisdomjobs.com/e-university/firebird-tutorial-210/general-form-of-order-by-clause-7812.html
Quote
In UNION and GROUP BY queries, the column used for sorting has to be present in the output list. In other queries, non-output columns and (in v.1.5) expressions on non-output columns are valid ordering criteria.
And it wouldn't explain his second try according to my suggestion to sort by PROGRAM_ID since this IS a output-column

EDIT: My money is on this IBQuery-Thing (never used it).
I've found some references to a Property "IndexFieldNames" (or some such) which would allow sorting on the Query after it has been called.
« Last Edit: October 27, 2021, 12:04:55 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: Firebird: Order by on join.
« Reply #5 on: October 27, 2021, 12:22:23 pm »
And it wouldn't explain his second try according to my suggestion to sort by PROGRAM_ID since this IS a output-column.

Correct. You're right.
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: Firebird: Order by on join.
« Reply #6 on: October 27, 2021, 01:32:50 pm »
Sorry, but TIBQuery works fine for me (with its Database and Transaction properties set; nothing more changed, in settings) with a query like this:

Code: MySQL  [Select][+][-]
  1. -- table A
  2. A.FIELD_ID, ...\...,
  3. -- table B
  4. B.FIELD_ID, ...\...,
  5. -- table C
  6. C.FIELD_ID, ...\...,
  7. -- table D
  8. D.FIELD_ID, ...\...,
  9. -- table E
  10. E.FIELD_ID, ...\...,
  11. -- table F
  12. F.FIELD_ID, ...\...
  13. TABLE_A AS A
  14.   LEFT OUTER JOIN TABLE_B AS B ON (A.FIELD_ID = B.FIELD_ID)
  15.     LEFT OUTER JOIN TABLE_C AS C ON (B.FIELD_ID = C.FIELD_ID)
  16.     LEFT OUTER JOIN TABLE_D AS D ON (B.FIELD_ID = D.FIELD_ID)
  17.     LEFT OUTER JOIN TABLE_E AS E ON (B.FIELD_ID = E.FIELD_ID)
  18.     LEFT OUTER JOIN TABLE_F AS F ON (B.FIELD_ID = F.FIELD_ID)
  19. A.FIELD_ID DESC; -- or ASC

I've replaced a TIBDataset by a TIBQuery, TIBQuery pointed by a datasource, datasource pointed by a TIBdynamicGrid: when I change the ORDER BY ASC to ORDER BY DESC in the SQL clause, the display order of the records visually changes in the dbGrid.

nb: I've a primary \ unique index on my primary keys, and simple b-tree secondary indexes on my foreign keys.
suggestion: publish a small project with the request inside.

               
« Last Edit: October 27, 2021, 01:44:22 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Firebird: Order by on join.
« Reply #7 on: October 27, 2021, 03:02:43 pm »
Take in the test the sorted field to see if it really is sorted.
Code: Pascal  [Select][+][-]
  1. Select EventData.EVENT_NO, EVENTDATA.PROGRAM_ID, EVENTDATA.PROGRAM_EVENT From PROGRAMDATA
  2. inner join EVENTDATA on
  3. PROGRAMDATA.Program_ID = EventData.Event_NO
  4. order by EventData.EVENT_NO;  
  5.  
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: Firebird: Order by on join.
« Reply #8 on: October 28, 2021, 12:21:02 am »
Gentlefolk,

Thank you for your responses. I have done some more work on this issue:

The SQL provided by mangakissa fails with complaints about unknown fields.

From the example provided by devEric69 I developed the following SQL which executes with no errors but does NOT sort the EVENTDATA table.

Select A.PROGRAM_ID, A.PROGRAM_EVENT from PROGRAMDATA as A
  LEFT OUTER JOIN EVENTDATA as B on (A.PROGRAM_ID = B.EVENT_NO)
order by A.Program_Event asc;

So as the results of the SQL select are transferred to a StringGrid I used

  OutGrid.SortOrder := soAscending;
  OutGrid.SortColRow(True, 2);

and things are sorted and displayed correctly....

I would however like to know how to use the SQl.

Thanks, ian.





Zvoni

  • Hero Member
  • *****
  • Posts: 2316
Re: Firebird: Order by on join.
« Reply #9 on: October 28, 2021, 08:26:15 am »
Sorry to say, but that SQL makes no sense.
You're exclusively selecting columns from PROGRAMDATA, LEFT JOIN-ing it with EVENTDATA, and sorting by a column from PROGRAMDATA.
You do realize that with a LEFT JOIN you get ALL records from the left table?

Leave out the LEFT JOIN....

SELECT PROGRAM_ID, PROGRAM_EVENT FROM PROGRAMDATA ORDER BY PROGRAM_EVENT
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: Firebird: Order by on join.
« Reply #10 on: October 28, 2021, 10:45:11 am »
There is nothing wrong (as far as I can see) with the SQL from the original post. And it should sort the way you described.

But I'm starting to believe there is something else wrong. How are you programming the SQL in the TIBQuery? In code or in the Object Inspector? Or both? Maybe you are filling the SQL in the wrong place and there is some override going on. (especially because you say it does work correctly in a SQL editor)

devEric69 already suggested it but it might be best you create a small project where you test your SQL yourself and if it's still giving problems you can post it here (via publish button in the IDE).

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: Firebird: Order by on join.
« Reply #11 on: October 29, 2021, 01:59:14 am »
Gentlefolk,
Thanks for the responses. I have to delay further work on this for a couple of days as some ugly weather with strong winds has caused damage to athletics facilities that I look after. off to work I go....
Ian

iru

  • Sr. Member
  • ****
  • Posts: 321
Re: Firebird: Order by on join.
« Reply #12 on: November 02, 2021, 03:36:22 am »
Gentlefolk,

I have been rather busy, strong winds around Melbourne last Thursday have caused extensive damage in many areas. There are still thousands of homes without electric power. 

The SQL is loaded in code.

There are two IBQuery:   IBQProgramData and  IBQEventData.
The SQL is written to IBQProgramData as follows:

        BaseDM.DMB.IBQProgramData.SQL.Clear;
        BaseDM.DMB.IBQProgramData.SQL.AddDelimitedText(cEventJoinSQL, '%', True);

Where cEventJoinSQL is a constant
cEventJoinSQL    =  'Select PROGRAM_ID, PROGRAM_EVENT From PROGRAMDATA %' +
                      'inner join EVENTDATA on PROGRAMDATA.Program_ID = EventData.Event_NO %' +
                      'order by EventData.EVENT_NO asc'; 

If I change 'order' to 'xxxorder' there is an ugly SQL error when the transaction on IBQProgramData is started.

Ian

Zvoni

  • Hero Member
  • *****
  • Posts: 2316
Re: Firebird: Order by on join.
« Reply #13 on: November 02, 2021, 07:47:57 am »
AddDelimitedText?????
Why the blazes are you using that?
Put the SQL in one line (no LineBreaks) and just use "..blablabla.SQL.Text:=" and be done with it
And remove the '%' from the SQL-String
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018