Forum > Database

[CLOSED] Firebird-SQL

<< < (2/4) > >>

egsuh:

--- Quote ---I‘m not even going to pretend understanding that design.
--- End quote ---

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
--- End quote ---

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  [+][-]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 s.*,         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Gender') Gender,         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Age') Age,         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Occp') Occp         FROM samples s

af0815:
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.

paweld:
@egush: I forgot to group
--- Code: SQL  [+][-]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 s.*, MAX(CASE WHEN sd.key_name='Gender' THEN sd.key_value ELSE NULL END) Gender,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) occpFROM SAMPLES s LEFT JOIN sample_data sd ON (s.pid=d.pid AND s.rid=d.rid)GROUP BY s.PID, s.RID, s.LOGIN_ID, s.PW, s.EMAIL, s.PHONE, s.FIRST_NAME, s.LAST_NAME

Zvoni:

--- Quote from: egsuh on April 03, 2023, 03:24:05 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.


--- End quote ---
Then define a column in the basetable and save that stuff as a JSON-Record

egsuh:

--- Quote ---hen define a column in the basetable and save that stuff as a JSON-Record
--- End quote ---

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.

Thank you for your concerns. 

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version