### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: [CLOSED] Firebird-SQL  (Read 5165 times)

#### egsuh

• Hero Member
• Posts: 1292
##### [CLOSED] Firebird-SQL
« on: April 03, 2023, 06:26:10 am »
I have two tables in Firebird. Is there any more concise SQL that'll do what I'd like to do? It's like a pivot-table, and select statement and result is in the image.

Code: SQL  [Select][+][-]
1. CREATE TABLE SAMPLES(
2.   PID INTEGER,
3.   RID INTEGER,
5.   PW VARCHAR(20),
6.   EMAIL VARCHAR(25),
7.   PHONE VARCHAR(20),
8.   FIRST_NAME VARCHAR(20),
9.   LAST_NAME VARCHAR(20)
10. );
11.
12. CREATE TABLE SAMPLE_DATA(
13.   PID INTEGER,
14.   RID INTEGER,
15.   KEY_NAME VARCHAR(20),
16.   KEY_VALUE VARCHAR(120)
17. );
18.
19. /* select statement */
20. SELECT s.*, d.key_value Gender, e.Key_value age, f.Key_value occp FROM SAMPLES s
21.
22.     LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Gender') d
23.     ON s.pid=d.pid AND s.rid=d.rid
24.
25.     LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Age') e
26.     ON s.pid=e.pid AND s.rid=e.rid
27.
28.     LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Occp') f
29.     ON s.pid=f.pid AND s.rid=f.rid
30.

« Last Edit: April 10, 2023, 07:25:29 am by egsuh »

#### Zvoni

• Hero Member
• Posts: 2327
##### Re: Firebird-SQL
« Reply #1 on: April 03, 2023, 08:55:00 am »
I‘m not even going to pretend understanding that design.
That‘s EAV Antipattern if i ever saw one.

To op‘s question: instead of subselects i‘d use CTE‘s but that‘s personal preference, and maybe a coalesce to get rid of the null‘s
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

#### af0815

• Hero Member
• Posts: 1291
##### Re: Firebird-SQL
« Reply #2 on: April 03, 2023, 10:48:47 am »
It is IMHO a pivot tranformation
I know this for firebird and it says it is not dynamic possible
https://stackoverflow.com/questions/55449169/is-there-any-way-to-pivot-rows-to-columns-dynamically-without-a-specific-no-of-c

MS-SQL have in the meantype such a feature
https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/

in the past i have done this with dynamic TSQL. This means i have computed the whole SQL-Statement by code and executed this. It was not effiency, because it was not 'pre' compilable (and only runtime optimation) for the mssql server.

(Or i was complete wrong with the pivot :-) )
regards
Andreas

#### ttomas

• Full Member
• Posts: 245
##### Re: Firebird-SQL
« Reply #3 on: April 03, 2023, 11:42:19 am »
Same result, but using indexes on pid, rid and key_name in sample_data, if exists.
Code: Pascal  [Select][+][-]
1. SELECT s.*
2.   , d.key_value Gender, e.Key_value age, f.Key_value occp
3. FROM SAMPLES s
4.
5.     LEFT JOIN sample_data d  ON (s.pid=d.pid AND s.rid=d.rid AND d.key_name='Gender')
6.
7.     LEFT JOIN sample_data e ON (s.pid=e.pid AND s.rid=e.rid AND e.key_name='Age')
8.
9.     LEFT JOIN sample_data f ON (s.pid=f.pid AND s.rid=f.rid AND f.key_name='Occp')
10.
« Last Edit: April 03, 2023, 11:46:09 am by ttomas »

#### paweld

• Hero Member
• Posts: 1003
##### Re: Firebird-SQL
« Reply #4 on: April 03, 2023, 01:07:34 pm »
something like this:
Code: SQL  [Select][+][-]
1. SELECT s.*, CASE WHEN sd.key_name='Gender' THEN sd.key_value ELSE NULL END Gender,
2. CASE WHEN sd.key_name='Age' THEN sd.Key_value ELSE NULL END age, CASE WHEN sd.key_name='Occp' THEN sd.Key_value ELSE NULL END occp
3. FROM SAMPLES s LEFT JOIN sample_data sd ON (s.pid=d.pid AND s.rid=d.rid)
Best regards / Pozdrawiam
paweld

#### egsuh

• Hero Member
• Posts: 1292
##### Re: Firebird-SQL
« Reply #5 on: April 03, 2023, 03:24:05 pm »
Quote
I‘m not even going to pretend understanding that design.

Yes, this design is ugly. I've not implemented yet. Just thinking over. Problem is the "property" names may change, so that I cannot fix fields at design time.

Quote
It is IMHO a pivot tranformation
I know this for firebird and it says it is not dynamic possible

You are right. This is pivot transformation. I know that MS-dbs support this functionality. But I do not need dynamic capability. I can make the SQL statement at Lazarus and query it.

@paweld:

Your suggestion is interesting. But not does not operate correctly for this case, I'm afraid. It produces three rows if all additional three columns are not null.

I have came up with another SQL statement. Cannot decide on which one is better between below and previous one using JOIN.  First principle: the simpler, the better! But which one is simpler? ^^

Code: SQL  [Select][+][-]
1.     SELECT s.*,
2.         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Gender') Gender,
3.         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Age') Age,
4.         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Occp') Occp
5.         FROM samples s

#### af0815

• Hero Member
• Posts: 1291
##### Re: Firebird-SQL
« Reply #6 on: April 03, 2023, 04:18:01 pm »
For me the working with case and a good sourcecode formatting is ok. If you want it more dynamic and query the possible values first, to generate a case statement on the fly is more simplier and can be better maintained IMHO.
regards
Andreas

#### paweld

• Hero Member
• Posts: 1003
##### Re: Firebird-SQL
« Reply #7 on: April 03, 2023, 04:30:23 pm »
@egush: I forgot to group
Code: SQL  [Select][+][-]
1. SELECT s.*, MAX(CASE WHEN sd.key_name='Gender' THEN sd.key_value ELSE NULL END) Gender,
2. MAX(CASE WHEN sd.key_name='Age' THEN sd.Key_value ELSE NULL END) age, MAX(CASE WHEN sd.key_name='Occp' THEN sd.Key_value ELSE NULL END) occp
3. FROM SAMPLES s LEFT JOIN sample_data sd ON (s.pid=d.pid AND s.rid=d.rid)
4. GROUP BY s.PID, s.RID, s.LOGIN_ID, s.PW, s.EMAIL, s.PHONE, s.FIRST_NAME, s.LAST_NAME
« Last Edit: April 03, 2023, 07:42:46 pm by paweld »
Best regards / Pozdrawiam
paweld

#### Zvoni

• Hero Member
• Posts: 2327
##### Re: Firebird-SQL
« Reply #8 on: April 03, 2023, 04:55:50 pm »

Yes, this design is ugly. I've not implemented yet. Just thinking over. Problem is the "property" names may change, so that I cannot fix fields at design time.

Then define a column in the basetable and save that stuff as a JSON-Record
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

#### egsuh

• Hero Member
• Posts: 1292
##### Re: Firebird-SQL
« Reply #9 on: April 04, 2023, 09:07:23 am »
Quote
hen define a column in the basetable and save that stuff as a JSON-Record

Yes. Actually I came up with this idea from reading mongoDB stuff.
I can save the definitions as JSON-format or simply I can save TStrings.Text (key=value pairs). This has no problem in using Lazarus applications.

What I need is to retrieve a subset of records, like samples under 35 years old, etc. So I'd like to define a view for every pid (because the key-names should be the same under a pid), and then use queries on it.

#### egsuh

• Hero Member
• Posts: 1292
##### Re: Firebird-SQL
« Reply #10 on: April 08, 2023, 06:44:39 am »
"JOIN" allows a little simpler syntax.

Code: SQL  [Select][+][-]
1.   SELECT s.*, Gender, Age, Occp
2.         FROM SAMPLES s
3.         LEFT JOIN (SELECT pid, rid, key_value Gender FROM sample_data WHERE key_name='Gender') USING (pid, rid)
4.         LEFT JOIN (SELECT pid, rid, key_value Age FROM sample_data WHERE key_name='Age') USING (pid, rid)
5.         LEFT JOIN (SELECT pid, rid, key_value Occp FROM sample_data WHERE key_name='Occp') USING (pid, rid);

#### rvk

• Hero Member
• Posts: 6163
##### Re: Firebird-SQL
« Reply #11 on: April 08, 2023, 08:07:59 am »
"JOIN" allows a little simpler syntax.

But this doesn't work if there are an unknown number values of key_value.
For that you would need to have real PIVOT or use an elaborate EXEC BLOCK.

#### Zvoni

• Hero Member
• Posts: 2327
##### Re: Firebird-SQL
« Reply #12 on: April 08, 2023, 08:35:18 am »
"JOIN" allows a little simpler syntax.

But this doesn't work if there are an unknown number values of key_value.
For that you would need to have real PIVOT or use an elaborate EXEC BLOCK.
Does Firebird have inhouse JSON-capabilities?
Because then it’s easy just to look for the key.
Can you even use parameters for that?
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: 6163
##### Re: Firebird-SQL
« Reply #13 on: April 08, 2023, 07:16:48 pm »
Does Firebird have inhouse JSON-capabilities?
Because then it’s easy just to look for the key.
Can you even use parameters for that?
No, not that I'm aware of.
But what use would that be to do PIVOT?

I was more thinking about something like this:
https://dbfiddle.uk/AUKBciR2

Code: SQL  [Select][+][-]
1. WITH CTE AS (
2. SELECT DISTINCT
3.   KEY_NAME,
4.   '(SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME=''' || KEY_NAME || ''') ' ZZ
5. FROM SAMPLE_DATA
6. )
7. SELECT 'SELECT X.*,'
8.    || CAST( LIST( CTE.ZZ) AS VARCHAR(3000))
9.    ||' FROM SAMPLES X'
10. FROM CTE

Which generates a SQL statement regardless of the number of distinct KEY_NAME values.
It will result in this:
https://dbfiddle.uk/LcK0YtsZ
with SQL statement:
Code: SQL  [Select][+][-]
1. SELECT
2.   X.*,
3.   (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Age') ,
4.   (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Gender') ,
5.   (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Occp')
6. FROM SAMPLES X
(I see I still need to add fieldnames but you get the idea)

And then let that resulting SQL execute in a EXECUTE statement.

I'm only not sure how to execute a generated statement in a block and returning the resultset.

#### Zvoni

• Hero Member
• Posts: 2327
##### Re: Firebird-SQL
« Reply #14 on: April 09, 2023, 12:23:18 am »
No, not that I'm aware of.
But what use would that be to do PIVOT?
I was more thinking along the lines that he could stow those records from his detail table as a JSON-Record in a single field of his master table, with only one entry per master record.

To pivot that thing he would query that same field 3 times in his select statement though with a different key.
And if it‘s possible to parametrize the key, he could avoid the EAV-Antipattern completely
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