#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    Angry What is wrong ???


    Hello friends.

    I execute the folow simple query on IBExpert (FireBird 2.5) and this work fine:

    SELECT
    pd.NOME_PRODUTO,
    es.QTDE_MINIMA
    FROM
    TBL_ESTOQUE es,
    TBL_PRODUTO pd
    WHERE
    es.qtde_estoque = 0
    AND es.produto = pd.id
    ORDER BY
    pd.NOME_PRODUTO

    But, if i create one stored procedure with too output parameters ( see bellow )

    begin
    SELECT
    pd.NOME_PRODUTO,
    es.QTDE_MINIMA
    FROM
    TBL_ESTOQUE es,
    TBL_PRODUTO pd
    WHERE
    es.qtde_estoque = 0
    AND es.produto = pd.id
    ORDER BY
    pd.NOME_PRODUTO
    into :nome_produto, :qtde_minima;
    suspend;
    end

    I get one worng message like this:
    " multiple rows in singleton select.
    multiple rows in singleton select.
    At proceddure 'SPD_SALDO_PROD_ZERADO_ESTOQUE' line: 7, col:3"

    What is this? I don't understanding what happen????

    Thank's a lot.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    Your select statment has more than 1 result and you have only one variable to set it.

    You have more than une product with qtde_estoque=0

    If you want a list of every product, you shoud use a "for select" statement:

    Code:
     
    CREATE PROCEDURE TEST (nome_produto varchar(15), qtde_minima integer)
    BEGIN
      FOR SELECT
         pd.NOME_PRODUTO,
         es.QTDE_MINIMA
      FROM
         TBL_ESTOQUE es,
         TBL_PRODUTO pd
      WHERE
         es.qtde_estoque = 0
         AND es.produto = pd.id
      ORDER BY
         pd.NOME_PRODUTO
         into :nome_produto, :qtde_minima
     do
     begin
        /* This returns one record */
        suspend;
     end;
    END;
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    Thank you my friend.

IMN logo majestic logo threadwatch logo seochat tools logo