I have a stored procedure where I h

[msng]

I have a stored procedure where I have the following query:


v_item_no := 0;

select min(item_no)
into v_item_no
from order_items
where status = 'A';

retCode := SQL%ROWCOUNT;

If the record does not exist in order_items table, then the value of v_item_no should be 0. ALso, retCode should have the value as 0.

However, this stored procedure returns the value of v_item_no as NULL and retCode as 1. WHY???

How can I fix it so that if there is no record found in order_items table, then the value of shelf_code should be 0. Also, retCode should return the value 0.

Please help.

However,

 

[dickiebird]

Use :

select nvl(min(item_no),0)
into v_item_no
from order_items
where status = 'A';

to get a 0(zero) returned in v_item_no - but you'll always have a row returned - its the nature of min,max,avg etc

HTH




Dickie Bird (-)))