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

If one operand is numeric, ORACLE always convert the other to a number. That's why a WHERE col = 10 clause would make no use of indexes if col is CHAR or VARCHAR2, once it's equivalent to WHERE TO_NUMBER(col)=10. This would cause the 1722 error you mentioned, if non-numeric values were found.

Once you stated that you'd already tried a non numeric restrictive clause (like WHERE col = '10', for instance), I guess your problem is related to the join you're performing on the CARID column. Maybe it's defined as numeric in the other table, so the join is performed using numeric comparision.

All the same, if you're not using ORACLE 6, it's not a good idea having CHAR columns. They are fixed length, and waste a lot of space. Change them to VARCHAR2, if you can.

--
Francisco Carlos Piragibe de Almeida
consultor ORACLE / aplicações WEB
 Dan Roberts escreveu no artigo ... ...


>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

>out??

>

>Here is the desc of the table

>SQL> DESC LS_DBA.CAR

> Name                            Null?         Type

> ------------------------------- -------- ----

>CARID                         NOT NULL    CHAR(10)

>CAR_TYPE                                      CHAR(8)

>WHEEL_TYPE                                 CHAR(1)

>

>

>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

>information..

>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

>and etc)

>

>the problem is that when I try to run the sql listed below..

>I get this

>

>result of SQL

>stuff snipped out

>'

>'

>'

>

>39961067                                                                  

 


>

>39961080                                                                  

 


>

>ERROR:

>ORA-01722: invalid number

>

>

>

>7969 rows selected.

>

>SQL>

>

>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..


>

>

>such as

>and car.carid

>between 3000000

>and 40000000

>

>or

>and car.carid

>>3000000

>and car.carid

><40000000

>

>or

>and car.carid

>>'3000000'

>and car.carid

><'40000000'

>

>

>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

>

>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

>

>--

>Dan Roberts

>BRISTOL-MYERS SQUIBB PHARM. RES. CENTER      "Nature is last at Bats"

>PRINCETON, NEW JERSEY U.S.A. PLANET EARTH, MILKY-WAY GALAXY

><<<<INTERNET ADDRESS>>>>Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM

>

>