RaqForum 28 No.
341 View  • 
     ...

Perform Recursive Query on a Same Table

 
recursive query(1)

Problem description & analysis

We have a database table SAMPLE. The data is as follows:

LINK

LINK_PATH

INFO

a

b

asdjh

w

y

akhaq

b

c

uiqwd

c

isado

y

z

qwiuu

z

nzabo

LINK_PATH is the parent level of LINK. Now we are trying to perform a recursive query on the table to find all parent records for, say, the record where LINK is a. Below is the desired result:

LINK

LINK_PATH

INFO

a

b

asdjh

b

c

uiqwd

c

isado

Solution

Write the following script p1.dfx in esProc:

A

1

=connect("demo")

2

=A1.query@x("SELECT * FROM SAMPLE")

3

>A2.switch(LINK_PATH,A2:LINK)

4

=A2.select@1(LINK==link).prior(LINK_PATH).run(LINK_PATH=LINK_PATH.LINK)

Explanation:

Set script parameter link, whose value is a.

A1   Connect to the database named demo.

A2  Return the query result as a table sequence and auto-close the database connection when the query is finished.

A3  Perform a self-join since LINK_PATH is the parent level of LINK.

A4  Find all parent records for the record where LINK is a according to the references in LINK_PATH values.

Read How to Call an SPL SCript in Java to learn about the method of integrating the SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/64146943/select-linked-rows-in-the-same-table


recursive query(1)
 
 •  341 View  •