Thank you LUCAS and EDUARDO,

If I do not place a SUSPEND command before IF statement, the result is
wrong...
Does SUSPEND means FETCH to Next record?



The Final version

CREATE PROCEDURE PR_BOMVIEW (
BOM_MALZEMEKODU VARCHAR (40))
RETURNS (
CHILD VARCHAR (40),
CHILDNAME VARCHAR (255))
AS
DECLARE VARIABLE HAS_CHILDREN SMALLINT;
BEGIN
FOR
SELECT B.CHILD,M.MALZEMEACIKLAMA FROM BOM B
JOIN MALZEME M
ON B.CHILD = M.MALZEMEKODU
WHERE B.PARENT = :BOM_MALZEMEKODU
INTO :CHILD,:CHILDNAME
DO
BEGIN
SUSPEND;
IF (EXISTS(SELECT CHILD FROM BOM b2 WHERE b2.PARENT=:CHILD)) THEN
BEGIN
FOR
SELECT CHILD,CHILDNAME FROM PR_BOMVIEW(:CHILD)
INTO :CHILD,:CHILDNAME
DO
SUSPEND;
END
END
END

----- Original Message -----
From: "Eduardo Resek" <eresek@...>
Newsgroups: egroups.ib-support
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, January 21, 2004 12:40 PM
Subject: Re: [firebird-support] MULTIPLE ROWS IN SINGLETON SELECT in SP


> You need a FOR ... SELECT, as the recursive procedure
> returns more then one line.
> BTW, I would replace the SELECT COUNT by a EXISTS
> clause in a IF statement:
>
> CREATE PROCEDURE PR_BOMVIEW (
> BOM_MALZEMEKODU VARCHAR (40))
> RETURNS (
> CHILD VARCHAR (40),
> CHILDNAME VARCHAR (255))
> AS
> DECLARE VARIABLE HAS_CHILDREN SMALLINT;
> BEGIN
> FOR
> SELECT B.CHILD,M.MALZEMEACIKLAMA FROM BOM B
> JOIN MALZEME M
> ON B.CHILD = M.MALZEMEKODU
> WHERE B.PARENT = :BOM_MALZEMEKODU
> INTO :CHILD,:CHILDNAME
> DO
> BEGIN
> IF (EXISTS(SELECT * FROM BOM b2 WHERE
> b2.PARENT=:CHILD)) THEN
> BEGIN
> FOR select child,childname from
> PR_BOMVIEW(:CHILD) INTO :CHILD,:CHILDNAME DO
> SUSPEND;
> END
> END
> END
>
> HTH,
>
> Eduardo Resek
> ----- Original Message -----
> From: Tanz Anthrox
> To: firebird-support@yahoogroups.com
> Sent: Wednesday, January 21, 2004 8:27 AM
> Subject: [firebird-support] MULTIPLE ROWS IN SINGLETON
> SELECT in SP
>
>
> The Stored Procedure shown below is prepared for Bill
> Of Material.
> But, the marked line has Multiple Rows sometimes and
> gives an error.
>
> How can I solve this problem? Any suggestion
>
>
> CREATE PROCEDURE PR_BOMVIEW (
> BOM_MALZEMEKODU VARCHAR (40))
> RETURNS (
> CHILD VARCHAR (40),
> CHILDNAME VARCHAR (255))
> AS
> DECLARE VARIABLE HAS_CHILDREN SMALLINT;
> BEGIN
> FOR
> SELECT B.CHILD,M.MALZEMEACIKLAMA FROM BOM B
> JOIN MALZEME M
> ON B.CHILD = M.MALZEMEKODU
> WHERE B.PARENT = :BOM_MALZEMEKODU
> INTO :CHILD,:CHILDNAME
> DO
> BEGIN
> SELECT COUNT(*) FROM BOM b2 WHERE b2.PARENT=:CHILD
> INTO :HAS_CHILDREN;
> IF (HAS_CHILDREN<>0) THEN
> BEGIN
> select child,childname from PR_BOMVIEW(:CHILD)
> INTO :CHILD,:CHILDNAME; /* THIS LINE CAUSES MULTIPLE
> ROWS IN SINGLETON SELECT */
> END
> SUSPEND;
> END
> END
>
>
>
> ______________________________________________________________________
>
> Yahoo! GeoCities: a maneira mais fácil de criar seu web site grátis!
> http://br.geocities.yahoo.com/
>
>
>
>
> Yahoo! Groups Links
>
> To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to:
> http://docs.yahoo.com/info/terms/
>
>