Re: Question Regarding Select Into

kumar wrote:
> Hi Mr. Frank
> Thanks for correcting me. That number was just an example as
> you rightly thought.
> As mentioned in the earlier mail, i had tried this as well.
>
> declare
> 2 a varchar2(30);
> 3 begin
> 4 select nvl(min(person_id),'asdf') into a from per_all_people_f
> where person_id = 110;
> 5* end;
>
> ERROR at line 1:
> ORA-01722: invalid number
> ORA-06512: at line 4
>
> It gives an invalid number error. Should min function be passed a
> number field or it can have any other datatype field as its argument?.
>

Person_id must be a number to use the MIN() function, yet you assign it to a varchar.
Also, you mix numbers and varchars in the nvl() function.

If person_id is a number, correct your code to read: declare
a number;
begin
  select nvl(min(person_id),0)
  into a
  from per_all_people_f
  where person_id = 110;
end;
/

as your where clause contains "person_id=110", I assume person_id to be a number.

Make sure, and then make your code consistent - do *not* use automatic type conversions; not only are these regarded sloppy code, it will also bite you. Either performance-wise, or code-wise ( "doesn't work" )

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...