Hi,

> I need a SP, view or Trigger to do following

A trigger? That would surprise me :-)

> when I choose one ItemCode as Parent. The Result must be;
>
> Child ID
> ---------- -----------
> Motor 1
> Cranck 2
> pipe 3
> head 4
> mat 5
> paint 6
> process 7
> Bearing 8
> Chain 9
> pod 10
>
>
> To Solve the proble I tried a SP shown Below But RECURSIVE CALL of itself

is not working

>
>
> CREATE PROCEDURE PR_BOMVIEW (
> BOM_ITEMCODE VARCHAR (40))
> RETURNS (
> ID INTEGER,
> CHILD VARCHAR (40))
> AS
> DECLARE VARIABLE COUNTER double PRECISION;
> BEGIN
> /* Procedure body */
> COUNTER = 1;
> FOR
> SELECT B.CHILD FROM BOM B WHERE B.PARENT = :BOM_ITEMCODE
> INTO :CHILD
> DO
> BEGIN
> ID = COUNTER;
> COUNTER = COUNTER+1;
> execute procedure PR_BOMVIEW(B.CHILD); /* THIS IS NOT WORKING */

If you use "execute", where do the output parameters go??

If you want to use SUSPEND, use "select ID, CHILD from PR_BOMIEW(B.CHILD)"

> SUSPEND;
> END
> END
>
>
> Also I need to check the infinite loops for it. But, I have no idea...

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com