Recent

Author Topic: [SOLVED] Simplefying SQL-statement  (Read 3098 times)

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
[SOLVED] Simplefying SQL-statement
« on: March 28, 2015, 04:25:07 pm »
Can any help me simplify this statement?
Code: [Select]
    sqlText := 'SELECT ' +
                 'tbl_Licenties.Fluit_ID, tbl_Licenties.Fluit_Scheids_ID, ' +
                 'tbl_Licenties.Fluit_Seizoen_ID, tbl_Licenties.Fluit_Licentie_ID, ' +
                 'REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +
                 'COALESCE(Tussenvoegsel || " ", "") || ' +
                 'COALESCE(Achternaam, "")), "  ", " ") AS Scheidsrechter, ' +
                 'tbl_Licenties_Keuze.Licentie, ' +
                 'tbl_Seizoenen.SEI_Periode' +
               'FROM ' +
                 'tbl_Licenties_Keuze INNER JOIN ((tbl_Licenties INNER JOIN ' +
                 'tbl_Scheidsrechters ON tbl_Licenties.Fluit_Scheids_ID = ' +
                 'tbl_Scheidsrechters.Scheids_ID) INNER JOIN tbl_Seizoenen ON ' +
                 'tbl_Licenties.Fluit_Seizoen_ID = tbl_Seizoenen.SEI_ID) ON ' +
                 'tbl_Licenties_Keuze.Licentie_ID = tbl_Licenties.Fluit_Licentie_ID ' +
               'WHERE ' +
                 'tbl_Licenties.Fluit_Scheids_ID<>19 AND ' +
                 'tbl_Licenties.Fluit_Scheids_ID=' + IntToStr(ID) + ' ' +
               'ORDER BY ' +
                 'REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +
                 'COALESCE(Tussenvoegsel || " ", "") || ' +
                 'COALESCE(Achternaam, "")), "  ", " "), tbl_Seizoenen.SEI_Periode DESC;';
[/code/
« Last Edit: April 04, 2015, 12:13:00 am by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Simplefying SQL-statement
« Reply #1 on: March 28, 2015, 04:55:45 pm »
First if you would post just the SQL statement separate from the pascal code it would be easier to read.

The join is a little hard to read with all of those brackets in the from clause. Try writing the from clause without brackets. The order by clause looks a little odd. Try using 'nulls first' or 'nulls last' instead of coalesce and concatenation.

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Simplefying SQL-statement
« Reply #2 on: March 28, 2015, 05:37:40 pm »
wow i though this was neat.
And if you can't separate the statement from the pascal wow.....

The part that i am interested in is the FROM part.


And i am asking a question because this is MS Access SQL and not SQLite.
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

rvk

  • Hero Member
  • *****
  • Posts: 6112
Re: Simplefying SQL-statement
« Reply #3 on: March 28, 2015, 06:50:12 pm »
It should look something like below. I did change the FROM to tbl_Licenties because I think it makes more sense to start from that table.

I do have a question. I gave you a detailed description (in the other topic) how you could convert a MS Access SQL statement (with all the extra nestings) to a proper SQL-statement. Wasn't that description clear? If so... what was not clear? I would like to know so we can teach you how to convert these statements yourself (without you having to ask us). Did you try it yourself using my description, and if so, where did you have trouble? (please don't see this remark as criticism)

Also you original query could never work in MS Access because you don't have a space before the FROM. See here:
    'tbl_Seizoenen.SEI_Periode' + 'FROM '
This will result in
tbl_Seizoenen.SEI_PeriodeFROM
which is not valid SQL.


(I changed the inttostr(ID) to 2222 to show you the "clean" SQL here)
Code: [Select]
SELECT
  tbl_Licenties.Fluit_ID,
  tbl_Licenties.Fluit_Scheids_ID,
  tbl_Licenties.Fluit_Seizoen_ID,
  tbl_Licenties.Fluit_Licentie_ID,
  REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || COALESCE(Tussenvoegsel || " ", "") || COALESCE(Achternaam, "")), "  ", " ") AS Scheidsrechter,
  tbl_Licenties_Keuze.Licentie,
  tbl_Seizoenen.SEI_Periode
FROM tbl_Licenties
INNER JOIN tbl_Licenties_Keuze ON tbl_Licenties.Fluit_Licentie_ID = tbl_Licenties_Keuze.Licentie_ID
INNER JOIN tbl_Scheidsrechters ON tbl_Licenties.Fluit_Scheids_ID  = tbl_Scheidsrechters.Scheids_ID
INNER JOIN tbl_Seizoenen       ON tbl_Licenties.Fluit_Seizoen_ID  = tbl_Seizoenen.SEI_ID
WHERE tbl_Licenties.Fluit_Scheids_ID<>19 AND tbl_Licenties.Fluit_Scheids_ID=2222
ORDER BY REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || COALESCE(Tussenvoegsel || " ", "") || COALESCE(Achternaam, "")), "  ", " "),
 tbl_Seizoenen.SEI_Periode DESC;

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Simplefying SQL-statement
« Reply #4 on: March 28, 2015, 09:12:51 pm »
No today i was just tired and lazy :) .
I will try it with the next statement that will give me trouble.
And if i can't handle it i will ask again and tell what i already have done ;)
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

rvk

  • Hero Member
  • *****
  • Posts: 6112
Re: Simplefying SQL-statement
« Reply #5 on: March 28, 2015, 09:17:26 pm »
No today i was just tired and lazy :) .
Haha, Ok  :)

 

TinyPortal © 2005-2018