Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Question Unanswered: Stored procedure error

    I am currently writing a stored procedure and receiving the following:

    A database manager error occurred.[IBM][CLI Driver][DB2] SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000

    I understand why I'm getting the error, however, based on the current contents of the table, the select statement should be returning two rows. My question is, how can I alter the SELECT statement so that I no longer receive an error?

    I'm on DB2 V7.1 (don't ask) on Z/OS platform.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Show us the code.(tm)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2007
    Posts
    56
    If Npdd_svs_id = (1, 2, 3, 4)
    Then
    Select E06_services_id,
    E06_pdd_svs_id,
    E06_rate_chg_id,
    E06_consumer_id,
    E06_budget_id,
    E06_funding_type,
    E06_rate_beg_dt,
    E06_rate_end_dt,
    E06_budgeted_units
    Into Servicesid_0,
    Pddsvsid_0,
    Ratechgid_0,
    Consumerid_0,
    Budgetid_0,
    Fundingtype_0,
    Ratebegdt_0,
    Rateenddt_0,
    Budgetedunits_0
    From Serdb.e06_services_t
    Where E06_pdd_svs_id = Npdd_svs_id
    And Nrate_eff_dt Between E06_rate_beg_dt And E06_rate_end_dt;

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    There has to be some business reason that you only get a single record. Do you want the one with the earliest date, most recent date? Should it be based on the svs_id in some order maybe the 1,2,3,4? We can tell you all kinds of ways to get a single row back, but you have to have some requirement as to which row your application should be retrieving. Or do you really want all those rows, in which case you need a cursor instead of a singleton select.
    Good luck.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by citi
    If Npdd_svs_id = (1, 2, 3, 4)
    Then
    I'm wondering: is "npdd_svs_id" a row or do you actually want to have "npdd_svs_id IN (1, 2, 3, 4)"? If neither is applicable, you will get a syntax error here.

    Select E06_services_id, ...
    Into Servicesid_0, ...
    From Serdb.e06_services_t
    Where E06_pdd_svs_id = Npdd_svs_id
    And Nrate_eff_dt Between E06_rate_beg_dt And E06_rate_end_dt;
    I would say that you WHERE clause does not uniquely identify a single row, causing the error to be raised. But that's hard to tell without the table definitions.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Aug 2007
    Posts
    56
    >>I'm wondering: is "npdd_svs_id" a row or do you actually want to have "npdd_svs_id IN (1, 2, 3, 4)"? If neither is applicable, you will get a syntax error here.

    Valid values for npdd_svs_id are 1-6. Rows that have a npdd_svs_id value of 1-4, and 5-6 will have their respective rows updated and a new row inserted based on business requirements.

    Rows also need to be selected based on whether or not the Nrate_eff_dt Between E06_rate_beg_dt And E06_rate_end_dt is true.

    Its also true that one or more rows will be selected.

    dav1mo mentioned using a cursor instead of a select. Does anyone have an example I could see?

    Thanks much.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by citi
    >>I'm wondering: is "npdd_svs_id" a row or do you actually want to have "npdd_svs_id IN (1, 2, 3, 4)"? If neither is applicable, you will get a syntax error here.

    Valid values for npdd_svs_id are 1-6. Rows that have a npdd_svs_id value of 1-4, and 5-6 will have their respective rows updated and a new row inserted based on business requirements.
    Then you already have a syntax error in the condition of the IF statement. How you were able to create the procedure, I do not know... I have the feeling that the code you have shown us does not match with the code you are using in your system that exhibits the problem.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Aug 2007
    Posts
    56
    Quote Originally Posted by stolze
    Then you already have a syntax error in the condition of the IF statement..
    ??? In what way?

    How you were able to create the procedure, I do not know... I have the feeling that the code you have shown us does not match with the code you are using in your system that exhibits the problem.[/QUOTE]

    What I have posted is the SELECT from the stored procedure. I'm using DB2's stored procedure builder.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This piece of code simply can't work:
    Code:
    If Npdd_svs_id = (1, 2, 3, 4) Then
    because you try to compare a single scalar value to a set of 4 values. You can either compare your variable with a single integer and combine this with OR predicates like "var = 1 OR var = 2 OR var = 3". Alternatively, you may want to use an IN predicate: "var IN ( 1, 2, 3, 4)".

    DB2 LUW complains about your syntax with the following error message:
    Code:
    $ db2 "create procedure p(in i int) begin if i = ( 1, 2, 3, 4 ) then begin end; end if; end"
    
    SQL0216N  The number of elements on each side of a predicate operator does not match.  Predicate operator is "=".  LINE NUMBER=1.  SQLSTATE=428C4
    I'll try DB2 z/OS later today, but I wouldn't be surprised if the same issue shows up as some sort of problem.

    p.s: I'm not using stored procedure builder; simply because it is great when you start working with DB2 stored procedures, but when you are familiar with the syntax, it doesn't offer much help any longer - except for debugging, though.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Here is what I see with DB2 z/OS V9:
    Code:
    CREATE PROCEDURE p(IN i INT, OUT o INT)
       LANGUAGE SQL
       BEGIN
          IF i = ( 1, 2, 3 ) THEN
             SET o = 0;
          ELSE
             SET o = 1;
          END IF;
       END
    
    SQL0216N  The number of elements on each side of a predicate operator does not
    match.  Predicate operator is "=".  SQLSTATE=428C4
    I haven't run V8 because this requires the precompilation of the procedure. If it would help you, I can give this a try, however.

    When you change the condition if the IF statement to "IF i = 1" for example, things are working fine.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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