Hi All,
Can anybody give me a solution how to solve or write a query for this scenario?

there is table called ITT1 with the columns

FATHER, CHILD_NUM, CODE, CODE_QTY
a 1 b 1
b 1 c 1.5
c 1 d 2
d 1 e 1

There are 3 columns father,child_num,code & the code_quantity.
It is explained as 'a' is my FINAL finished good and is produced from raw material 'b' using 1kg.
and here 'b' becomes my finished good and is produced from raw material 'c' using 1kg.
and here 'c' becomes my finished good and is produced from raw material 'd' using 1kg.
and here 'd' becomes my finished good and is produced from raw material 'e' using 1kg.

now i want how much raw material 'e' is required to produced for eg. 20kg of 'a'

should i use connect by prior or sub queries will help in this regards.
kindly advice.

Regards,
Faheem

select code_qty *10 from ITT1 where code=
(select father from ITT1 where code=
	(select father from ITT1 where code = 
		(select father from ITT1 where code = 'a')
	)
)