Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

Thread: recursieve sql met firebird 2.1

  1. #1

    recursieve sql met firebird 2.1

    Voor zover ik kan vinden zou het mogelijk moeten zijn om met firebird 2.1 een recursieve query te maken
    Ik heb een tabel met daarin een aantal producten die een identiefier hebben en een unieke ID.
    Daarnaast hebben ze ook een parent ID die aangeeft bij welke parent ze horen

    Identifier 12345 heeft dus als childs de ABC-123 (ID =3) en de sht-003 (ID=7)
    De ABC-123 heeft dan weer de lot-1234 en de plk_001 als child

    Graag zou ik een query ontwerpen die alle items teruggeeft die gebruikt zijn in de identifier 12345

    Tot nu toe heb ik de volgende query opgesteld
    Code:
     with recursive Child as (select Parent_ID As P from MES_TRACEABILITY where Identifier = 12345
                              union all
                              select Child.* from Child, MES_TRACEABILITY
                              where Child.P = MES_TRACEABILITY.IDNR  
     )   
     select * from MES_TRACEABILITY M2
    Dit geeft echter de melding
    Can't format message 13:896 -- message file C:\Program Files (x86)\EMS\firebird.msg not found.
    Dynamic SQL Error.
    SQL error code = -104.
    Unknown ISC error 336397237. //CTE "@1" is not used in query

    Helaas kan ik hier nog niet zo veel mee.

    Heeft iemand een idee wat ik fout doe

    MES_TRACEABILITY
    ID ParentID Identifier TekNr Datum RegNR
    1 0 12345 171M100 00 2023-0-10 4266
    2 0 12346 171M200 00 2023-0-10 4266
    3 1 ABC-123 171M005 00 2023-0-10 4266
    4 2 ABC-124 171M005 00 2023-0-10 4266
    5 0 12347 171M200 00 2023-0-10 4266
    6 3 plk_001 171M117 00 2023-0-10 4266
    7 1 sht-003 171M144 00 2023-0-10 4266
    8 3 lot-1234 020R200 01 2023-0-10 4266
    9 4 lot-1234 020R200 01 2023-0-10 4266

  2. #2
    mmmhhh wellicht is een plaatje van de tabel duidelijker (mijn HTML is wat roestig om deze hier op het forum netjes op te maken)

    Click image for larger version. 

Name:	recursieve traceability.png 
Views:	234 
Size:	14.7 KB 
ID:	8307

  3. #3
    Quote Originally Posted by cpri View Post
    Dit geeft echter de melding
    Can't format message 13:896 -- message file C:\Program Files (x86)\EMS\firebird.msg not found.
    Dynamic SQL Error.
    SQL error code = -104.
    Unknown ISC error 336397237. //CTE "@1" is not used in query
    Volgens mij is je probleem ook dat je door die missende firebird.msg hele slechte foutmeldingen krijgt.

    Als ik dit in fiddle invoer dan krijg ik al gelijk wat opmerkelijke fouten in je SQL.

    Je gebruikt Parent_ID terwijl je tabel ParentID heeft.
    Je gebruikt MES_TRACEABILITY.IDNR terwijl die tabel alleen .ID heeft.
    Je doet Identifier = 12345 maar verderop in de tabel zie ik dat IDENTIFIER ook characters in zich heeft en dus een stringveld is. Dan kun je niet = 12345 doen maar moet je = '12345' doen.
    Je doet na de RECURSIVE gewoon een normale SELECT op MES_TRACEABILITY. Maar dat resulteert in de normale tabel. Je wilt waarschijnlijk FROM CHILD daar doen.

    Enfin... hier is een fiddle waarmee je wat makkelijker verder kunt werken:

    https://dbfiddle.uk/1LwWGUHU

    Zorg er wel voor dat je in je UNION ALL SELECT ook een veld hebt waar bij de volgende recursie op gechecked kan worden.
    Hier in de Fiddle gebruik ik gewoon T.ID en hernoem dat veld niet naar P, want dat heb je in je UNION ALL ook niet en volgens mij werkt het dan niet.

    Result van de Fiddle
    Code:
    1 0 12345    171M100 00 10.10.2023 4266
    3 1 ABC-123  171M005 00 10.10.2023 4266
    6 3 plk_001  171M117 00 10.10.2023 4266
    8 3 lot-1234 020R200 01 10.10.2023 4266
    7 1 sht-003  171M144 00 10.10.2023 4266
    SQL Code:
    1. WITH RECURSIVE T AS (
    2.     SELECT *
    3.     FROM TEST
    4.     WHERE IDENTIFIER = '12345'
    5.     UNION ALL
    6.     SELECT *
    7.     FROM T
    8.     INNER JOIN TEST R ON R.PARENTID=T.ID
    9. )
    10. SELECT * FROM T

  4. #4
    Hartelijk dank voor het meedenken.
    Had me nooit gerealiseerd dat er een probleem was met EMS.
    Ga dit zeker ook oplossen (scheelt een hoop frustratie denk ik zo)

    Mijn excuus bij de conversie van de Excel file naar de Firebird tabel ben ik wat onzorgvuldig geweest in de naamgeving vandaar dat sommige veldnamen net iets anders zijn

    Ik ga aan de slag met jouw tips

  5. #5
    Super, heb de query omgeschreven naar mijn tabelnamen en alles werkt nu.\
    Volgende stap is nu natuurlijk om niet enkel van boven naar beneden te gaan maar ook vanbendene naar boven dus in welke artikelen wordt bijv lot-1234
    Hierbij ben ik niet zo zeer opzoek naar item ABC-123 Maar juist naar de artikelen waarbij de parent 0 is.

    Gaan we eens rustig over nadenken maar tips zijn altijd welkom

  6. #6
    SQL Code:
    1. WITH RECURSIVE T AS (
    2.     SELECT *
    3.     FROM TEST
    4.     WHERE IDENTIFIER = 'plk_001'
    5.     UNION ALL
    6.     SELECT *
    7.     FROM TEST
    8.     INNER JOIN T ON T.PARENTID=TEST.ID
    9. )
    10. SELECT * FROM T
    En als je dan alleen de base-parent wilt hebben dit erachter
    SQL Code:
    1. WHERE PARENTID=0

  7. #7
    Inmiddels heb ik het nu weten samen tevoegen tot 1 query waarbij je dus zowel de parents als ook childs kan vinden

    Code:
    select * FROM
    (
     WITH RECURSIVE Childs AS 
     (
        SELECT *
        FROM TEST
        WHERE IDENTIFIER = 'lot-1234'
        UNION ALL
        SELECT *
        FROM Childs
        INNER JOIN TEST R ON R.PARENTID=Childs.ID
     )
     SELECT * FROM Childs
    )
    
    UNION
    
    select * FROM
    (
     WITH RECURSIVE Parent AS 
     ( 
        SELECT *
        FROM TEST
        WHERE IDENTIFIER = 'lot-1234'
        UNION ALL
        SELECT *
        FROM TEST
        INNER JOIN Parent ON Parent.PARENTID=TEST.ID
     )
     SELECT * FROM Parent
    )
    Om te voorkomen dat de database in een oneindige loop komt wil ik daarvoor een maximale diepte instellen
    Nu kan ik dus wel het niveau achterhalen maar helaas stopt de query dan niet

    Code:
     WITH RECURSIVE Childs AS 
     (
        SELECT ID, ParentID,Identifier,TekNR,DATUM,RegNr, 1 as Niveau
        FROM TEST
        WHERE IDENTIFIER = '12345'
        UNION ALL
        SELECT ID, ParentID,Identifier,TekNR,DATUM,RegNr, Niveau+1 as Niveau
        FROM Childs
        INNER JOIN TEST R ON R.PARENTID=Childs.ID
        WHERE Niveau<3
     )
     SELECT * FROM Childs
    Wanneer ik deze code uitvoer zie ik toch nog steeds de entries die op niveau 3 zitten terug in mijn resultaat
    Code:
    6 3 plk_001  171M117 00 10.10.2023 4266
    8 3 lot-1234 020R200 01 10.10.2023 4266
    Blijkbaar doe ik iets nog niet helemaal juist

  8. #8
    Quote Originally Posted by cpri View Post
    Code:
     WITH RECURSIVE Childs AS 
     (
        SELECT ID, ParentID,Identifier,TekNR,DATUM,RegNr, 1 as Niveau
        FROM TEST
        WHERE IDENTIFIER = '12345'
        UNION ALL
        SELECT ID, ParentID,Identifier,TekNR,DATUM,RegNr, Niveau+1 as Niveau
        FROM Childs
        INNER JOIN TEST R ON R.PARENTID=Childs.ID
        WHERE Niveau<3
     )
     SELECT * FROM Childs
    Wanneer ik deze code uitvoer zie ik toch nog steeds de entries die op niveau 3 zitten terug in mijn resultaat
    Code:
    6 3 plk_001  171M117 00 10.10.2023 4266
    8 3 lot-1234 020R200 01 10.10.2023 4266
    Blijkbaar doe ik iets nog niet helemaal juist
    Die plk_001 en lot-1234 zitten volgens mij op niveau 2, niet niveau 3.
    Je moet rekenen vanaf niveau 0 (dat is zonder parent).
    Volgens mij komt dat omdat die Niveau+1 pas in de iteratie erna gebeurd (in de recursie).

    Dus als je die 2 uit wilt sluiten moet je WHERE Niveau<2 doen.

    Maar als alles goed is zou hij toch niet in een oneindige loop mogen komen (als je query goed is) ?

  9. #9
    Mhhh bluikbaar wekt het wel alleen is het dus MAxniveau + 1

    Wanneer ik het maximale niveau op <2 zet dan wordt het derde niet meer meegenomen.

    Aangezien ik nu via de join alle inputs 2 keer moet ingeven (identifier en het liefst ook het max niveau) is er een kans dat de childs op een andere identifier gezocht worden dan de parents.
    Uiteraard kan je dit oplossen door bijv de identifier regel aan te passen met een parameter
    Code:
      WHERE IDENTIFIER =:aIdentifier
    Echter ik wil dit zelf in mijn sql stetament op kunnen geven zonder dat de gebruiker hier naar gevraagd wordt.
    Kan ik nu ergens in het sql statement zelf een waarde toekennen aan aIdentifier

  10. #10
    Bedankt voor de uitleg!!

    Maar als alles goed is zou hij toch niet in een oneindige loop mogen komen (als je query goed is) ?
    Tja als het goed is kan dit idd niet gebeuren maar dat vind ik dus altijd het probleem met recursive functies.
    Zoalng de data idd via een interface wordt toegevoegd aan de database en niemand er mee gaat zitten rommelen gaat het goed.
    Maar als er iemand van de parentID van de '12345' ipv 0 de waarde 3 meegeeft dan zit je toch mooi in de problemen denk ik zo.

    Zal dat morgen op mijn test server eens proberen
    (Vermoed dat men niet vrolijk wordt wanneer ik dit op de productie server uitprobeer ;-) )

  11. #11
    Quote Originally Posted by cpri View Post
    Maar als er iemand van de parentID van de '12345' ipv 0 de waarde 3 meegeeft dan zit je toch mooi in de problemen denk ik zo.
    Nee, het gaat volgens mij alleen mis als jij een fout in je SQL hebt zitten.
    Als je 0 ingeeft, dan krijg je gewoon geen resultaat terug (als je SQL goed is).

    Quote Originally Posted by cpri View Post
    Wanneer ik het maximale niveau op <2 zet dan wordt het derde niet meer meegenomen.
    Ja.

    Ik denk dat je het zo moet zien... de WHERE bepaald wanneer je die iteratie doet.
    Dus wanneer je Niveau < 3 hebt, dan wordt de iteratie bij 2 nog uitgevoerd en krijg je ook de 3 niveau waardes.

    Kijk maar eens als je er Niveau = 1 van maakt.
    Dan krijg je de niveau 1 en 2 waardes.

    Maar als je er Niveau = 2 van maakt dan krijg je TOCH de niveau 1 waarde omdat deze bovenin als basis select staat.
    De waardes van Niveau 2 krijg je dan niet omdat aan die voorwaarde nooit voldaan wordt (want in de eerste iteratie is niveau 1 en zonder volgende recursie wordt dat niet opgehoogd).
    WHERE niveau = 5 krijg je dus ook altijd die basis van Niveau 1.

    Je moet de WHERE Niveau < dus zien als voorwaarde wanneer die de volgende iteratie moet doen.

    Leuk hé, RECURSIVE :lol:

    Edit: O, er kan natuurlijk nog wel een loop in zitten. Je kunt een item weer naar dezelfde parent laten verwijzen en in de data dus een loop creëren :lol:

    Last edited by rvk; 13-Feb-23 at 13:03.

  12. #12
    Voor die laatste loop ben ik dus bang (en ga ik niet op mijn productie server testen)

  13. #13
    Quote Originally Posted by cpri View Post
    Aangezien ik nu via de join alle inputs 2 keer moet ingeven (identifier en het liefst ook het max niveau) is er een kans dat de childs op een andere identifier gezocht worden dan de parents.
    Uiteraard kan je dit oplossen door bijv de identifier regel aan te passen met een parameter
    [...]
    Echter ik wil dit zelf in mijn sql stetament op kunnen geven zonder dat de gebruiker hier naar gevraagd wordt.
    Kan ik nu ergens in het sql statement zelf een waarde toekennen aan aIdentifier
    Nog even hierop terugkomend.

    Je kunt ook zoiets doen. Je roept in de basis RECURSIVE in de eerste SELECT alle ParentID=0 maar geeft dan ook gelijk ID en IDENTIFIER mee in de SELECT.
    In de subSELECT voor CHILDS pak je diezelfde BASE ID en BASE IDENTIFIER.
    Je krijgt dan ALLE rows maar je krijgt twee extra kolommen met de ultimate parent.

    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 ID, ParentID,Identifier,TekNR,DATUM,RegNr, Niveau+1 AS Niveau, BASE, BASE_IDENTIFIER
    8.     FROM Childs
    9.     INNER JOIN TEST R ON R.PARENTID=Childs.ID
    10.     WHERE Niveau<10
    11.  )
    12.  SELECT * FROM Childs

    Result:
    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
    Uiteraard kun je hier helemaal onder een WHERE opnemen van welke subchild of welke parent je wilt hebben.

  14. #14
    Mhhh anders dan dat de titel doet vermoeden heb ik dit ook even op firebird 3.0 getest.
    De kale query zonder de beveiliging doet het daarop prima
    Zodra ik ook het veld Niveau wil toevoegen moet ik dus alle velden uitschrijven.
    Echter hierna krijg ik de foutmelding

    Ambigious fields found between Test en Childs

    Hierop heb ik dus in de selects steeds expliciet aangegeven van welke tabel het veld afkomstig is maar dat resulteerd weer in de volgende foutmelding (in Firebird 2.1 krijg ik dan ook dezelfde foutmelding)
    De code staat eronder. Hoe krijg ik nu ok mijn sql code werkend op Firebird 3.0

    Too many concurrent executions of the same request.

    Code:
      WITH RECURSIVE Childs AS 
     (
        SELECT T.ID, T.ParentID,T.Identifier,T.TekNR,T.DATUM,T.RegNr,1 as niveau
        FROM TEST T 
        WHERE T.IDENTIFIER = 'ABC-123'
        UNION ALL
        SELECT Childs.ID, Childs.ParentID,Childs.Identifier,Childs.TekNR,Childs.DATUM,Childs.RegNr, niveau+1 as niveau
        FROM Childs 
        INNER JOIN TEST R ON R.PARENTID=Childs.ID     
     )
     SELECT * FROM Childs

  15. #15
    Quote Originally Posted by cpri View Post
    Too many concurrent executions of the same request.
    [/code]
    In Flamerobin komt ie in een loop als je Childs. voor alle velden zet in de recursive select.


    Code:
    3	1	ABC-123	171M005 00	10.10.2023	4266	1
    3	1	ABC-123	171M005 00	10.10.2023	4266	2
    3	1	ABC-123	171M005 00	10.10.2023	4266	3
    3	1	ABC-123	171M005 00	10.10.2023	4266	4
    3	1	ABC-123	171M005 00	10.10.2023	4266	5
    3	1	ABC-123	171M005 00	10.10.2023	4266	6
    3	1	ABC-123	171M005 00	10.10.2023	4266	7
    3	1	ABC-123	171M005 00	10.10.2023	4266	8
    3	1	ABC-123	171M005 00	10.10.2023	4266	9
    3	1	ABC-123	171M005 00	10.10.2023	4266	10
    3	1	ABC-123	171M005 00	10.10.2023	4266	11
    3	1	ABC-123	171M005 00	10.10.2023	4266	12
    3	1	ABC-123	171M005 00	10.10.2023	4266	13
    3	1	ABC-123	171M005 00	10.10.2023	4266	14
    3	1	ABC-123	171M005 00	10.10.2023	4266	15
    3	1	ABC-123	171M005 00	10.10.2023	4266	16
    3	1	ABC-123	171M005 00	10.10.2023	4266	17
    etc

Page 1 of 2 1 2 LastLast

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
  •