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/