recursive query met firebird 2.1

[Christian Prins]

Hi all,

As far as I know it should be possible to write a recursive query with FB2.1

I have a table containing products with their identifier, they also have a UID
Besides they can have a parent ID pointing to their associate parent

So the childs of Identifier 12345 are ABC-123 (ID =3) and the sht-003 (ID=7)
ABC-123 contains than lot-1234 and the plk_001 as a child

Now I like to design a query that gives me all the childs of the identifier 12345
Here is my query thus far

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

When I try to run this query I got the following error message
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

What is it that I'm doing wrong

The table I use is shown below in text and as a picture

I hope you can help me out on this one

Thanks in advance

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

[Steve Naidamast]

Here is a RECURSIVE stored-procedure which I wrote for my document management system that retrieves all of a user-defined set of category nodes based on an input key...

CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY(
  PI_KEY_IN BIGINT NOT NULL)
RETURNS(
  PI_KEY_OUT BIGINT,
  PI_PARENT_KEY_OUT BIGINT)
AS
BEGIN
  FOR
    WITH RECURSIVE HIERARCHY_TABLE AS
    (
        SELECT RCN1.CN_KEY,
               RCN1.CN_PARENT_KEY
            FROM CD_CATEGORY_NODES RCN1
            WHERE RCN1.CN_KEY = :PI_KEY_IN
            UNION ALL
                SELECT RCN2.CN_KEY,
                       RCN2.CN_PARENT_KEY
                    FROM CD_CATEGORY_NODES RCN2
                    JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY
    )
    SELECT CN_KEY,
           CN_PARENT_KEY
        FROM HIERARCHY_TABLE
        INTO :PI_KEY_OUT,
             :PI_PARENT_KEY_OUT
  DO
    BEGIN
      IF (PI_KEY_IN = 0) THEN
        EXCEPTION ROOT_CAT_NODE_DELETE;
         
      SUSPEND;
    END      
END

Here is the table structure it is applied against...

CREATE TABLE CD_CATEGORY_NODES (
  CN_KEY BIGINT NOT NULL,
  CN_PARENT_KEY BIGINT NOT NULL,
  CN_ROOT_KEY BIGINT NOT NULL,
  CN_DEPTH INTEGER NOT NULL,
  CN_CATEGORY VARCHAR(500) NOT NULL)

Maybe this will give you an idea of what you are doing incorrectly with your own SQL code...

Steve Naidamast

Sr. Software Engineer

[Karol Bieniaszewski]

>> What is it that I'm doing wrong

 

You have only declared you recursive query but you do not use it in the query itself. Your query is:

 

>> select * from MES_TRACEABILITY M2

 

Which do not use your CTE named „Child”.

 

Regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/c399a93a-01e8-4075-be94-07b1eacdecben%40googlegroups.com.

 

[Rik van Kekem]

Op zaterdag 11 februari 2023 om 16:32:41 UTC+1 schreef Christian Prins:

As far as I know it should be possible to write a recursive query with FB2.1

I have a table containing products with their identifier, they also have a UID
Besides they can have a parent ID pointing to their associate parent

I've already answered this on the Dutch forum (it seems to be down at this moment).

See the Firebird Fiddle at https://dbfiddle.uk/1LwWGUHU

WITH RECURSIVE T AS (
    SELECT *
    FROM TEST
    WHERE IDENTIFIER = '12345'
    UNION ALL
    SELECT *
    FROM T
    INNER JOIN TEST R ON R.PARENTID=T.ID
)
SELECT * FROM T

Will result in:

ID    PARENTID    IDENTIFIER    TEKNR    DATUM    REGNR
1    0    12345    171M100 00    2023-10-10    4266
3    1    ABC-123    171M005 00    2023-10-10    4266
6    3    plk_001    171M117 00    2023-10-10    4266
8    3    lot-1234    020R200 01    2023-10-10    4266
7    1    sht-003    171M144 00    2023-10-10    4266