Sorry, 3 categories = 3 layer
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 2. Mai 2017 08:36 An: firebird-support@yahoogroups.com Betreff: AW: [firebird-support] CTE Question Hi SET, I would like to specify this: Table A: CREATE TABLE T_L_PRKOM_GRP ( ID_GRP INTEGER NOT NULL, ID_KOM INTEGER NOT NULL, POS INTEGER, ID INTEGER NOT NULL ); Example: INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID) VALUES (1, 2, 1, 15); INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID) VALUES (8, 2, 2, 16); INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID) VALUES (9, 2, 3, 17); COMMIT WORK; Table B: CREATE TABLE T_L_PRKOM_GRP_POS ( KENNZEICHEN VARCHAR(10) NOT NULL COLLATE DE_DE, BEZEICHNUNG VARCHAR(50) COLLATE DE_DE, ID INTEGER NOT NULL, ID_KOM_GRP INTEGER, PRICE DOUBLE PRECISION ); Examples: INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('00', NULL, 28, 15, 5, 1, 2, 1, 15); INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('01', NULL, 29, 15, 6, 1, 2, 1, 15); INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('02', NULL, 30, 15, 7, 1, 2, 1, 15); INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('55', NULL, 32, 16, 1, 8, 2, 2, 16); INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('56', NULL, 33, 16, 2, 8, 2, 2, 16); INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('57', NULL, 34, 16, 3, 8, 2, 2, 16); INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('88', NULL, 35, 17, 2, 9, 2, 3, 17); INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('89', NULL, 36, 17, 3, 9, 2, 3, 17); INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP, PRICE, ID_GRP, ID_KOM, POS, ID1) VALUES ('90', NULL, 37, 17, 4, 9, 2, 3, 17); COMMIT WORK; Now we have 2 categories, 3 layers and I would like every combination like this: list(pos), List(id from t_l_prkom_grp_pos), list(price), List(kennzeichen) pos(1,2,3), id(28,32,35), price(5,1,2), (00,55,88) pos(1,2,3), id(28,32,36), price(5,1,3), (00,55,89) pos(1,2,3), id(28,32,37), price(5,1,4), (00,55,90) pos(1,2,3), id(28,33,35), price(5,2,2), (00,56,88) .. Pos(1,2,3), id(30,34,37), price(7,3,4), (02,57,90) I can create with this informations records like this Part-number, price.. 005588, 8 Euro.. 005589, 9 Euro.. 005590, 10 Euro.. 005688, 9 Euro .. 025790, 14 Euro My test procedure works not in every case, I get the following (not after 3 category-combinations a return and not every informations: create or alter procedure P_TMP_LITESA returns ( A varchar(3000), B varchar(3000), C varchar(3000), D varchar(3000)) AS declare variable kennung varchar(10); declare variable id_kom_grp integer; declare variable tmp_kennzeichen varchar(10); declare variable tmp_pos integer; declare variable pos2 integer; declare variable pos integer; declare variable tmp1 varchar(100); declare variable tmp2 varchar(100); declare variable tmp3 varchar(10); declare variable tmp_pos1 integer; declare variable i integer = 0; begin -- teilenr = ''; -- select kennung from t_l_prkom where id = 2 into :kennung; for with recursive ok as (select a.pos, b.kennzeichen, b.price, b.id from t_l_prkom_grp a left join t_l_prkom_grp_pos b on a.id = b.id_kom_grp where a.id_kom = 2 union all select c.pos, d.kennzeichen, d.price, d.id from t_l_prkom_grp c left join t_l_prkom_grp_pos d on c.id = d.id_kom_grp inner join ok on c.pos = ok.pos+1 where c.id_kom = 2 order by pos) select list(pos), list(kennzeichen), list(price), list(id) from ok into :a, :b, :c, :d do begin suspend; end /* select pos, kennzeichen from ok into :idid, :k do begin /* if(tmp_pos1 = 8) then begin suspend; teilenr = left(teilenr,char_length(teilenr)-(2*(8-:idid+1))); end teilenr = teilenr || k; tmp_pos1 = idid; end */ End How can I optimize this? Thank you. Mit freundlichen Grüßen / with best regards Olaf Kluge S A T R O N Sachsen Steuerungstechnik GmbH Johann-Gottlob-Pfaff Straße 7 D-09405 Zschopau Tel: +49 (0) 3725 / 3506-31 Fax: +49 (0) 3725 / 3506-12 Mobil: +49 (0) 170 / 9292375 E-Mail: mailto:olaf.kl...@satron.de Internet: http://www.satron.de/ ............................................................................ ................ Geschäftsführer: Bernd Grötzschel, Gerd Kaden Amtsgericht: Chemnitz HRB1218 Ust-ID-Nr: DE141294791 ............................................................................ ................ Diese E-Mail ist vertraulich. Wenn Sie nicht der beabsichtigte Empfänger sind, dürfen Sie die Informationen nicht offen legen oder benutzen. Wenn Sie diese E-Mail durch einen Fehler bekommen haben, teilen Sie uns dies bitte mit, indem Sie die E-Mail an den Absender zurücksenden. Bitte löschen Sie danach diese E-Mail. This email is confidential. If you are not the intended recipient, you must not disclose or use the information contained in it. If you have received this mail in error, please tell us immediately by return email and delete the document. -----Ursprüngliche Nachricht----- Von: mailto:firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Freitag, 28. April 2017 22:17 An: mailto:firebird-support@yahoogroups.com Betreff: Re: [firebird-support] CTE Question Hi Olaf! First, SQL doesn't like unknown columns, you need to know at least the maximum possible number of properties to support. Having said that, you could try something like: select B1.Property, B2.Property, B3.Property, B4.Property from TableA A1 join TableB B1 on A1.ID = B1.ID_TableA left join TableA A2 on A2.Pos = 2 left join TableB B2 on A2.ID = B2.ID_TableA left join TableA A3 on A3.Pos = 3 left join TableB B3 on A3.ID = B3.ID_TableA left join TableA A4 on A4.Pos = 4 left join TableB B4 on A4.ID = B4.ID_TableA left join TableC C on C.ID_TableB_1 in (A1.ID, A2.ID, A3.ID, A4.ID) and (C.ID_TableB_2 in (A1.ID, A2.ID, A3.ID, A4.ID) or C.ID_TableB_2 is null) and (C.ID_TableB_3 in (A1.ID, A2.ID, A3.ID, A4.ID) or C.ID_TableB_3 is null) and (C.ID_TableB_4 in (A1.ID, A2.ID, A3.ID, A4.ID) or C.ID_TableB_4 is null) where A1.Pos = 1 and C.ID is null Since the left joins to TableA doesn't refer to other tables, I assume the left joins between TableA and TableB to be the same as cross joins. TableC is for your next step, I'm assuming TableC could be defined like: ID, ID_TableB_1, ID_TableB_2, ID_TableB_3, ID_TableB_4 and that not red, 60W could be written like: 1, 101, 109, <null>, <null> (assuming 101 to be red and 109 to be 60W) It is of course thinkable that TableC also could have rows rather than columns for properties like TableB, but that would make the query more complex. HTH, Set ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links