Page 2 of 2 FirstFirst 1 2
Results 16 to 17 of 17

Thread: recursieve sql met firebird 2.1

  1. #16
    PS. Als je overigens TEST. pakt voor de velden dan gaat het wel goed :lol:

    SQL Code:
    1. WITH RECURSIVE Childs AS
    2.  (
    3.     SELECT ID, ParentID,Identifier,TekNR,DATUM,RegNr, 1 AS Niveau, ID AS BASE, IDENTIFIER AS BASE_IDENTIFIER
    4.     FROM TEST
    5.     WHERE ParentID = 0
    6.     UNION ALL
    7.     SELECT TEST.ID, TEST.ParentID, TEST.Identifier, TEST.TekNR, TEST.DATUM, TEST.RegNr, Niveau+1 AS Niveau, BASE, BASE_IDENTIFIER
    8.     FROM Childs
    9.     INNER JOIN TEST ON TEST.PARENTID=Childs.ID
    10.     WHERE Niveau<20
    11.  )
    12.  SELECT * FROM Childs

    Code:
    1	0	12345	171M100 00	10.10.2023	4266	1	1	12345
    3	1	ABC-123	171M005 00	10.10.2023	4266	2	1	12345
    6	3	plk_001	171M117 00	10.10.2023	4266	3	1	12345
    8	3	lot-1234	020R200 01	10.10.2023	4266	3	1	12345
    7	1	sht-003	171M144 00	10.10.2023	4266	2	1	12345
    2	0	12346	171M200 00	10.10.2023	4266	1	2	12346
    4	2	ABC-124	171M005 00	10.10.2023	4266	2	2	12346
    9	4	lot-1234	020R200 01	10.10.2023	4266	3	2	12346
    5	0	12347	171M200 00	10.10.2023	4266	1	5	12347

  2. #17
    Ook in Firebird 2.1 kreeg ik loop wanneer ik er Child. voor plakte.
    Heb nu dezelfe truc toegepast en nu werkt de query zowel in FB 2.1 als ook in FB 3

    Heb de truc ook toegepast op het parent gedeelte

    Code:
    select * FROM
    (
     WITH RECURSIVE Childs AS 
     (
        SELECT ID, ParentID,Identifier,TekNr,DATUM,RegNr, 1 as Niveau
        FROM TEST T
        WHERE IDENTIFIER = '12345'
        UNION ALL
        SELECT R.ID, R.ParentID,R.Identifier,R.TekNr,R.DATUM,R.RegNr, Niveau+1 as Niveau
        FROM Childs
        INNER JOIN TEST R ON R.PARENTID=Childs.ID
        WHERE NiVEAU<10
     )
     SELECT * FROM Childs
    )
    
    UNION
    
    select * FROM
    (
     WITH RECURSIVE Parent AS 
     ( 
        SELECT ID, ParentID,Identifier,TekNR,DATUM,RegNr, 1 as Niveau
        FROM TEST T1
        WHERE IDENTIFIER = '12345'
        UNION ALL
        SELECT T2.ID, T2.ParentID,T2.Identifier,T2.TekNR,T2.DATUM,T2.RegNr, 1 as Niveau
        FROM TEST T2
        INNER JOIN Parent ON Parent.PARENTID=T2.ID
        WHERE NiVEAU<10
     )
     SELECT * FROM Parent
    )

Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •