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

Hi,

missed the original posting, just saw the reply from Fransisco Piragibe, so not sure of the original requirement. But, for what its worth ....

You could try the following

   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 to_number( decode(translate(car.carid,'0123456789','XXXXXXXXX'),
                    'XXXXXXXXXX',car.carid,0))
          between 3000000
          and 40000000

The translate converts all digits to X's. The decode tests if the result is all X's, ie. the original was a number, and if so it returns car.carid to to_number. If the carid contains non-digits, the translate does not return all X's,so the decode returns 0 to to_number, and the row is rejected.

There are 10 X's in the first string. The second string of X's is as long as the number of characters in a carid field. If the carid field can contain a variable number of characters, replace the second string of X's by:

     substr('XXXXXXXXXXXX',1,length(car.carid))

Sorry, its messy and not efficient ( can't use an index on car.carid )

but hope it helps.

Dave. Received on Thu Jul 31 1997 - 00:00:00 CDT