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.

Show us the code.(tm)

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;

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.

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.

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.

>>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.

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.

??? 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.

This piece of code simply can't work:
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:
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.

Here is what I see with DB2 z/OS V9:
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.