Hi Netters..I am serching the following table for a series of records
which match the SQL stmt below...but I eventually get an ORA-1722
error..and I can't seem to get around this..Maybe someone can help me
Here is the desc of the table
SQL> DESC LS_DBA.CAR
Name Null? Type
------------------------------- -------- ----
CARID NOT NULL CHAR(10)
I am searching this car table for records based on some criteria...and I
am having trouble getting back all the records that I expect..
above is the table desc.
ok...now what I am basically looking to do is get all the CARID's from the
latest release of the car database...so I can fish out other car related
This table has a huge number of records..about 1.2 million or so..as you
can tell the CARIDs are of CHAR(10) datatype...The reason this was done
(and not declared as NUM) is because a small portion of these records have
CARIDs that begin generally with two alphabetic chars (AB1334 or f93495
the problem is that when I try to run the sql listed below..
I get this
result of SQL
stuff snipped out
ORA-01722: invalid number
7969 rows selected.
here is my sql..I have tried a variety of things..when it comes to the
part where I want to select the range of CARIDs for which I am searching..
It seems that Oracle just can't cope with automatic data conversion from
char to num when doing its comparisons and it runs into a carid that
begins with alphanumerics and not numbers.......you have any idea how I
can get over this ORA=1722 error?? I have fooled around with the
to_number function..but no luck there...what I was thinking of doing was
nested select stmts where I would select all CARID's that where LIKE ..and
would have digits in the first position..but I can't find a pattern
matching operator that is valid for only digits..
Have any thoughts that would help me out???.>Thanks!!!!!!! Dan
The complete sql query
and car.car_type is NULL
and bodytype.carfabricmethod =500
BRISTOL-MYERS SQUIBB PHARM. RES. CENTER "Nature is last at Bats"
PRINCETON, NEW JERSEY U.S.A. PLANET EARTH, MILKY-WAY GALAXY