Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TEST (
     ID INTEGER NOT NULL,
     PARENTID INTEGER,
     IDENTIFIER VARCHAR(20),
     TEKNR VARCHAR(20),
     DATUM DATE,
     REGNR INTEGER);
EXECUTE BLOCK AS BEGIN
INSERT INTO TEST VALUES(1,0,'12345'   ,'171M100 00' ,'2023-10-10',4266);
INSERT INTO TEST VALUES(2,0,'12346'   ,'171M200 00' ,'2023-10-10',4266);
INSERT INTO TEST VALUES(3,1,'ABC-123' ,'171M005 00' ,'2023-10-10',4266);
INSERT INTO TEST VALUES(4,2,'ABC-124' ,'171M005 00' ,'2023-10-10',4266);
INSERT INTO TEST VALUES(5,0,'12347'   ,'171M200 00' ,'2023-10-10',4266);
INSERT INTO TEST VALUES(6,3,'plk_001' ,'171M117 00' ,'2023-10-10',4266);
INSERT INTO TEST VALUES(7,1,'sht-003' ,'171M144 00' ,'2023-10-10',4266);
INSERT INTO TEST VALUES(8,3,'lot-1234','020R200 01' ,'2023-10-10',4266);
INSERT INTO TEST VALUES(9,4,'lot-1234','020R200 01' ,'2023-10-10',4266);
END
9 rows affected

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


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

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