stored procedure "686 Function has returned more than one row."

kumarshetty · #1 (**permalink**) 07-11-08, 10:57

Hi All,

this is the sample code of my issue.

CREATE PROCEDURE A(int,int)
RETURNING int,int,int,int,int,int ;
-
-- here I define variables
-
FOREACH cur_a FOR
SELECT
-
-
FROM
-
-
INTO
-
-
WHERE
-
-
CALL PROCEDURE B(int,int)
RETURNING int,int,int ;
-
-
-- here iam returning the values of procedure B also
RETURN a,b,c,x,y,z WITH RESUME ;
END FOREACH
END PROCEDURE ;

=======================

CREATE PROCEDURE B(int,int)
RETURNING int,int,int ;
-
-- here i define variables
-
FOREACH cur_b FOR
SELECT
-
-
INTO
-
-
WHERE
-
-
RETURN x,y,z WITH RESUME ;
END FOREACH
END PROCEDURE ;

====================================

I have a requirement in which I call procedure A which returns multiple rows and for each row I have to call a procedure B which in turn returns multiple rows.

I dont get any syntax error in any of procedure.

When I call the procedure B from procedure A, I get below error message.

"686: Function B has returned more than one row."

If I call the procedure B with sample values, it executes as it is expected.

It seems I have to change my code in procedure A to handle the return value of procedure B or might be i have to change logic of procedure A.

I am new to informix and please help me in this issue.

thanks,
-- kumar

ceinma · #2 (**permalink**) 07-14-08, 13:10

What version of IDS you are using?

Always you use the "RETURN...WITH RESUME", this means your procedure will behave like a sql return. So, this way you can't return multiple lines inside only one variable!

You need to change the call of procedureB .

If you are using IDS 11.x you can access the procedure like this example :

Code:

create table teste(
  cod integer, desc char(10), tipo char(1)
  );
insert into teste values (1,'um',"a");
insert into teste values (2,'dois',"a");
insert into teste values (3,'tres',"b");
insert into teste values (4,'quatro',"b");
insert into teste values (5,'cinco',"c");

create procedure procb(pTipo char(1)) returning int,char(10);
  define valA integer;
  define valB char(10);
  foreach cursorA for
    select cod, upper(desc) into valA, valB from teste
    where tipo = pTipo
    return valA,valB with resume ;
  end foreach
  ;
end procedure
;
create procedure procA() returning int,char(10);
  define vTipo char(1);
  define valA  int;
  define valB  char(10);
  foreach cursorA for
    select unique tipo into vTipo from teste
    order by 1 desc
    foreach cursorB for
      select col1,col2 into valA, valB from table(procb(vTipo)) (col1,col2)
      return valA, valB with resume
      ;
    end foreach
    ;
  end foreach
  ;
end procedure
;
select * from table(proca()) myVirtual_table (colA,colB);

I am not sure, but with version 10 , maybe this works too.

kumarshetty · #3 (**permalink**) 07-21-08, 21:38

Thanks, Ceinma....it has resolved my issue.

Internet Services

Web Development

Digital Marketing

Consumer Tech