Subject AW: [firebird-support] CTE Question
Author Olaf Kluge
Post date 2017-05-02T12:26:20Z

Hello,

 

Ive testet this, but it is’nt the best solution, I think:

 

..and why does I get the last positions (combinations) from the recursive cte twice?

 

create or alter procedure P_TMP_LITESA

returns (

    A varchar(3000),

    B varchar(3000),

    C varchar(3000),

    D varchar(3000),

    E 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;

declare variable i1 integer;

declare variable i2 integer;

declare variable i3 integer;

declare variable d1 double precision;

declare variable t1 varchar(10);

declare variable zae integer;

declare variable del integer = 0;

declare variable maxpos integer;

begin

-- teilenr = '';

-- select kennung from t_l_prkom where id = 2 into :kennung;

a = ',';

b = ',';

c = ',';

d = ',';

for with recursive ok as

(select a.pos, b.kennzeichen, b.price, b.id from t_l_prkom_grp a inner 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 inner join t_l_prkom_grp_pos d on c.id = d.id_kom_grp

join ok on c.pos = ok.pos+1

where c.id_kom = 2 order by pos)

 

select pos, kennzeichen, price, id from ok into :i1, :t1, :d1, :i2 do

begin

 

if (del = 1) then

begin

   del = 0;

   zae = 3-i1+1;

   while (zae > 0) do

   begin

   a = left(a, char_length(a)-position(',',reverse(a),2)+1);

   b = left(b, char_length(b)-position(',',reverse(b),2)+1);

   c = left(c, char_length(c)-position(',',reverse(c),2)+1);

   d = left(d, char_length(d)-position(',',reverse(d),2)+1);

   zae = zae -1;

   end

end

a = a || :i1 || ',';

b = b || :t1 || ',';

c = c || :d1 || ',';

d = d || :i2 || ',';

if(i1 = 3) then  -- später maxpos

begin

   del = 1;

   suspend;

 

 

--   teilenr = left(teilenr,char_length(teilenr)-(2*(8-:idid+1)));

end

-- teilenr = teilenr || k;

 

 

end

 

 

end

 

I would like to create a record with all categories after the loop is on the last point for each combination and I take the string (,1,23,21,..) and set each into a new record oft he piece.

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.kluge@...
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