CODEWITHSUNDEEP
oracle-databaseplsql
Sushant Butta
Sushant Butta

Reputation: 530

Parent child relationship query in Oracle

I have table like this

Table- A

Conversion_logic       Output_param

func(a,b)               c

func(d)                 e

func(c)                 d

func(e)                 f

Here rows depicts that func(a,b) gives me "c", now this "c" is applied as func(c) and gives me "d", now this "d" is applied as func(d) and gives me "e" and now this "e" is applied as func(e) which gives me "f"

So I want an output like this

1) That row should be the first row whose output has no dependency.
2) From there it should follow parent child relation

Conversion_logic       Output_param
func(e)                f
func(d)                e
func(c)                d
func(a,b)              c

Upvotes: 0

Views: 1817

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

Try

SELECT t1.*
  FROM taba t1
  LEFT OUTER JOIN taba t2 ON instr(replace(t1.Conversion_logic, 'func'), t2.Output_param) > 0
START WITH t2.Conversion_logic IS NULL
CONNECT BY instr(replace(t1.Conversion_logic, 'func'), PRIOR t1.Output_param) > 0
ORDER BY LEVEL DESC

Here is a sqlfiddle demo

Upvotes: 0

Noel
Noel

Reputation: 10525

You can use hierarchical queries.

SELECT     table_name.*
  FROM table_name
CONNECT BY PRIOR conversion_logic = 'func(' || output_param || ')'
START WITH conversion_logic = 'func(e)';

UPDATE:

SELECT     table_name.*
  FROM table_name
CONNECT BY PRIOR conversion_logic = 'func(' || ooutput_param || ')'
START WITH ooutput_param =
          (SELECT a.ooutput_param
             FROM table_name a
            WHERE 'func(' || a.ooutput_param || ')' NOT IN (
                                                 SELECT b.conversion_logic
                                                   FROM table_name b));

Not sure about the performance of this query. Maybe there are better and efficient ones.

Upvotes: 2

Related Questions