Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008
    Posts
    3

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

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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.
    Last edited by ceinma; 07-14-08 at 14:20.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Jul 2008
    Posts
    3
    Thanks, Ceinma....it has resolved my issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •