Home » SQL & PL/SQL » SQL & PL/SQL » control cannot go to exception (oracle 10g)
control cannot go to exception [message #404419] Thu, 21 May 2009 14:44 Go to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
hi, in a procedure i have below code ( didnot write complete code)

  SELECT min(seq) 
  INTO   p_seq 
  FROM   table_a
  WHERE  id = p_id 
         AND stat = p_stat; 

EXCEPTION 
  WHEN no_data_found THEN 
    p_seq := 99999;


if i pass value(p_id) to the procedure, and if there is no value for seq for the passed p_id, then the control should go to exception and assign 99999 to p_seq. but its not hapenning.

if i select seq instead of min(seq) in the select query, then the control is going to exception and assigning 99999 to p_seq.

cant we use exception if we are using group functions like min,max etc??

thanks
jillu
Re: control cannot go to exception [message #404420 is a reply to message #404419] Thu, 21 May 2009 14:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
cant we use exception if we are using group functions like min,max etc??

This exception does not happen with min.
Use "select nvl(min(...),9999) ..."

Regards
Michel
Re: control cannot go to exception [message #404425 is a reply to message #404419] Thu, 21 May 2009 15:14 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
i got the answer. if we use aggregate function in select query, it always return a value or a null value. hence control doesnt go to no_data_found exception.

thanks
jillu
Re: control cannot go to exception [message #404426 is a reply to message #404425] Thu, 21 May 2009 15:15 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
sorry michel, i didnot see your response .

thans anyway
Re: control cannot go to exception [message #404543 is a reply to message #404425] Fri, 22 May 2009 03:57 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
nastyjillu wrote on Thu, 21 May 2009 21:14
if we use aggregate function in select query, it always return a value or a null value.


That should be if we use aggregate function without a group by.
If you use a group by you can get no_data_found.
Previous Topic: Summary Materialized views
Next Topic: Sequence having number with character
Goto Forum:
  


Current Time: Mon Dec 05 02:50:59 CST 2016

Total time taken to generate the page: 0.13656 seconds