Re: SQL and ORA-1722 error?? Seeking Your Help

Dan Roberts wrote:
>
> The complete sql query
>
> select car.carid
> from
> ls_dba.car, ls_dba.bodytype
> where
> ls_dba.car.carid=ls_dba.bodytype.carid
> and car.car_type is NULL
> and bodytype.carfabricmethod =500
> and car.carid
> between 3000000
> and 40000000
>

create the following function:

 create or replace
 function number_of( candidate varchar ) return number is  begin

    return to_number( candidate );
 exception

    when others then

       return 0; -- or "return NULL"  end;

Now the SQL we could use would then be:

 select car.carid
 from ls_dba.car, ls_dba.bodytype
 where ls_dba.car.carid=ls_dba.bodytype.carid

    and car.car_type is NULL
    and bodytype.carfabricmethod = 500
    and number_of( car.carid ) between 3000000 and 40000000

If you create the function in a package you have to use the restrict_references pragma for at least WNDS and (I think) RNDS.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"