### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

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

• Hero Member
• Posts: 823
• ..... A day not Laughed is a day wasted !!
##### [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 2.0.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

#### 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.

• Hero Member
• Posts: 823
• ..... A day not Laughed is a day wasted !!
##### 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 2.0.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

#### rvk

• Hero Member
• Posts: 4327
##### 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_PeriodeFROM tbl_LicentiesINNER JOIN tbl_Licenties_Keuze ON tbl_Licenties.Fluit_Licentie_ID = tbl_Licenties_Keuze.Licentie_IDINNER JOIN tbl_Scheidsrechters ON tbl_Licenties.Fluit_Scheids_ID  = tbl_Scheidsrechters.Scheids_IDINNER JOIN tbl_Seizoenen       ON tbl_Licenties.Fluit_Seizoen_ID  = tbl_Seizoenen.SEI_IDWHERE tbl_Licenties.Fluit_Scheids_ID<>19 AND tbl_Licenties.Fluit_Scheids_ID=2222ORDER BY REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || COALESCE(Tussenvoegsel || " ", "") || COALESCE(Achternaam, "")), "  ", " "), tbl_Seizoenen.SEI_Periode DESC;`

• Hero Member
• Posts: 823
• ..... A day not Laughed is a day wasted !!
##### 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 2.0.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

#### rvk

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