SQL and ORA-1722 error?? Seeking Your Help

SQL and ORA-1722 error?? Seeking Your Help

Post by Dan Rober » Tue, 22 Jul 1997 04:00:00



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

Quote:>3000000

and car.carid
<40000000

or
and car.carid

Quote:>'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

 
 
 

SQL and ORA-1722 error?? Seeking Your Help

Post by Wayne Linto » Tue, 22 Jul 1997 04:00:00


check for a NULL value, blanks, or some other non-numeric value in your
carid.

Wayne Linton
Shell Canada Ltd.

 
 
 

SQL and ORA-1722 error?? Seeking Your Help

Post by Tomm Car » Tue, 22 Jul 1997 04:00:00



> 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!"

 
 
 

SQL and ORA-1722 error?? Seeking Your Help

Post by Francisco Piragib » Fri, 25 Jul 1997 04:00:00


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


 
 
 

SQL and ORA-1722 error?? Seeking Your Help

Post by Dave Wotto » Fri, 01 Aug 1997 04:00:00


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.