CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Wesley Long
Wesley Long

Reputation: 1708

Can't seem to find record by simple ID query

I have a view from some tables:

SELECT UM00200M.ACCOUNT_NO AS INDEX1,
          CONCAT (CONCAT (TRIM (UM00200M.PERSON_LNM), ' '),
                  TRIM (UM00200M.PERSON_FNM))
             AS INDEX2,
          DECODE (NVL (TRIM (SG00100M.PERSON_ID_CUSTOM), 0),
                  0, UM00200M.PERSON_NO,
                  SG00100M.PERSON_ID_CUSTOM)
             INDEX3,
          NULL AS INDEX4,
          'CONS_ACCTG' AS GROUPNAME
     FROM UM00200M, SG00100M
    WHERE UM00200M.PERSON_NO = SG00100M.PERSON_NO

Which gives me (Sorry for the formatting):

Column_Name   DATA_TYPE       NULLABLE   COLUMNT_ID  COMMENTS    INSERTABLE  UPDATEABLE 
INDEX1        NUMBER(14,0)    No         1           YES         YES         YES
INDEX2        VARCHAR2(81)    Yes        2           NO          NO          NO
INDEX3        NUMBER          Yes        3           NO          NO          NO
INDEX4        VARCHAR2(0)     Yes        4           NO          NO          NO
GROUPNAME     CHAR(10)        Yes        5           NO          NO          NO

I am looking for all the records that have INDEX3 = 524118914

--Looking for INDEX3 = 524118914

--Fails:  01722. 00000 -  "invalid number"
Select *
from Cayenta.CAHeader 
where INDEX3 = 524118914;

--Fails:  01722. 00000 -  "invalid number"
Select *
from Cayenta.CAHeader 
where INDEX3 > 524118000 and INDEX3 < 524118999;

--Works
Select *
from Cayenta.CAHeader 
where INDEX3 > 524118000 and INDEX3 < 524999999; 

--Fails: 01722. 00000 -  "invalid number"
Select *
from Cayenta.CAHeader 
where INDEX3 > 524118000 and INDEX3 < 524999999 
ORDER BY INDEX3;

I don't get the results that I expect when I execute the query. Why would I get "invalid number?"

I thought it would be something to do with the DECODE function, and SG00100M.PERSON_ID_CUSTOM is a CHAR(15 BYTE) field, but filled with all numeric data. (I know - bad design, but it's a 3rd-party product.)

I appreciate any insights anyone can offer.

Upvotes: 0

Views: 144

Answers (2)

Mike Meyers
Mike Meyers

Reputation: 2895

What's happening here is implicit conversion. This means that in the condition in the where clause, you are comparing the column INDEX3 with a number.

INDEX3 is a CHAR column so the following excerpt from the Oracle document applied with respect to implicit conversion:

During arithmetic operations on and comparisons between character and noncharacter datatypes, Oracle converts from any character datatype to a numeric, date, or rowid, as appropriate

This means that all the values from INDEX3 will be converted to a NUMBER in order to allow them to be compared to the number that you have given in the example.

The simple way to get around this is to use quotes around the number and treat it as a character string.

where INDEX3 = '524118914'

This will work for the equality condition but may not work so well for the ranges using >= and <= unless they are padded with zeroes at the beginning. Using the correct data types also means that any indexes on this column will be used.

If you think that this should be a number field, then there is at least one row that has nonnumeric data in it. To track this down you could try checking the data by using the following condition:

where regexp_like(INDEX3, '[^[:digit:]]')

This only checks for numbers (not decimal points etc) so you may need to tailor it to your requirements.

I was a little confused about why the database was saying that the column has a numeric data type. It seems that this is related to the way that the DECODE statement is evaluated and the data type comes from the first result argument, even though the default argument is a CHAR type.

Upvotes: 1

int2000
int2000

Reputation: 565

Storing Numbers in Character-Fields is always a pain...

Please try the following SQL for the View - The decodes checks PERSON_ID_CUSTOM for a Number. If it is, in converts it, if not - it'll be replaced by NULL:

    SELECT UM00200M.ACCOUNT_NO AS INDEX1,
              CONCAT (CONCAT (TRIM (UM00200M.PERSON_LNM), ' '),
                      TRIM (UM00200M.PERSON_FNM))
                 AS INDEX2,
              DECODE (NVL (TRIM (SG00100M.PERSON_ID_CUSTOM), 0),
                      0, UM00200M.PERSON_NO,
decode((
  REPLACE(
   TRANSLATE(
    TRIM(SG00100M.PERSON_ID_CUSTOM),'0123456789','00000000000'
   ),'0' ,NULL
  )
 ),NULL,to_number(trim(SG00100M.PERSON_ID_CUSTOM)))
) INDEX3,
              NULL AS INDEX4,
              'CONS_ACCTG' AS GROUPNAME
         FROM UM00200M, SG00100M
        WHERE UM00200M.PERSON_NO = SG00100M.PERSON_NO

Upvotes: 0

Related Questions