| ****ORA-01422****Need Help!! [message #37322] |
Wed, 30 January 2002 18:03  |
Esther
Messages: 2
Registered: January 2002
|
Junior Member |
|
|
Anyone knows about this ORA-01422 error message?
My pl/sql script is called itemlist.sql, it is used to select some data from a Baan database.
---------------------------------------------------------------------------------------------------------------------------------------------------
declare
ONHAND NUMBER;
/* this cursor select all planned stock movement. */
/* twhinp100 contains all planned stock movement transactions. */
cursor Itemlist is
SELECT substr(replace(t100.t$item, ' '), 1, 20) item,
t100.t$cwar warehouse,
t100.t$date thedate,
'A' status,
t100.t$orno orderno,
t100.t$koor ordertype,
t100.t$kotr transtype,
t100.t$bpid bp,
t100.t$qana qty
from twhinp100350 t100;
begin
for T1 in ItemList /* use loop to fetch records one by one from the cursor */
Loop
select t$stoc into onhand from twhwmd215350 /* select the inventory onhand for the item in that warehouse */
where T1.item = replace(t$item, ' ')
and T1.warehouse = t$cwar;
end loop;
end;
/
---------------------------------------------------------------------------------------------------------------------------------------------------
I run the script.
SQL> @itemlist
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 38
However, if I don't select the item 'C8507A#AB2' in the cursor, the script works fine. Here is the script without selecting the item.
---------------------------------------------------------------------------------------------------------------------------------------------------
declare
ONHAND NUMBER;
/* this cursor select all planned stock movement. */
/* twhinp100 contains all planned stock movement transactions. */
cursor Itemlist is
SELECT substr(replace(t100.t$item, ' '), 1, 20) item,
t100.t$cwar warehouse,
t100.t$date thedate,
'A' status,
t100.t$orno orderno,
t100.t$koor ordertype,
t100.t$kotr transtype,
t100.t$bpid bp,
t100.t$qana qty
from twhinp100350 t100
where t100.t$item not like '%C8507A#AB2%';
begin
for T1 in ItemList /* use loop to fetch records one by one from the cursor */
Loop
select t$stoc into onhand from twhwmd215350 /* select the inventory onhand for the item in that warehouse */
where T1.item = replace(t$item, ' ')
and T1.warehouse = t$cwar;
end loop;
end;
/
---------------------------------------------------------------------------------------------------------------------------------------------------
The only difference between these two scripts is the second cursor doesn't include the 'C8507A#AB2' item. And it works fine.
SQL> @itemlist
PL/SQL procedure successfully completed.
Is there something wrong with this item's (C8507A#AB2)database set up? What can I do to solve the problem?
Well, I definitely need some help on this.
Best Regards,
|
|
|
|
|
|
| Re: ****ORA-01422****Need Help!! [message #38095 is a reply to message #37322] |
Tue, 19 March 2002 11:30  |
Wally Sanya
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
You can either do select max(value) from ...
if this does not apply to what you are doing..
this should do:
add
AND ROWNUM = 1;
to the end of your query. The probelem is the value you are selecting has duplicate rows and this causes it to blow up.
Let me know how it goes.
Good luck.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
