ORA-01722: invalid number error

Question

I am running the below mentioned query in my Oracle client and i am getting

ORA-01722: invalid number

error. I know the issue is due to the TAG_VALUE column being of type "varchar2" and i am converting it to number and then using that field in where clause. I have tried using "CAST" function but that is also not helping. If i run the query neglecting the last where condition with code WHERE (P.TAG_VALUE > '100') then i am getting the result but including the last where clause gives me error.

 SELECT DISTINCT
      count(P.CREATED_DATETIME)
    FROM
      ( 
      select OUTPUT_TAG_ID,TO_NUMBER(TAG_VAL,'9999.99') AS 
    TAG_VALUE,TAG_VAL_TS,CREATED_DATETIME
    from OV80STG.PRCSD_DATA_OUTPUT_ARCHIVE
    where MODEL_CODE='MDLADV1538'
    AND TAG_VAL <> 'U_Transfer_rate'
      )   P
    WHERE
    (P.TAG_VALUE > '100')

Any suggestion will be appreciated. Thanks.

@Arpit . Can you check if the query SELECT * FROM OV80STG.PRCSD_DATA_OUTPUT_ARCHIVE WHERE REGEXP_LIKE(TAG_VALUE, '[^[:digit:]]') returns any rows?. It is to check if non-numeric characters exists in the column — Kaushik Nayak
Thanks for replying Jens, i have tried doing that but still the query returns same error. — Arpit Arora
@ Kaushik, thanks for your reply, yes the mentioned query is returning rows — Arpit Arora
@ArpitArora : It means there are non-numeric characters in TAG_VALUE column. You must replace them from all those records fetched. — Kaushik Nayak

dbajtr dbajtr · Accepted Answer · 2017-09-06T08:06:16

Remove the single quotes from around the value in the where, you don't need them when its an integer. query will be like this:

SELECT DISTINCT
    COUNT(P.CREATED_DATETIME)
FROM
(
    SELECT
        OUTPUT_TAG_ID,
        TO_NUMBER(TAG_VAL, '9999.99') AS TAG_VALUE,
        TAG_VAL_TS,
        CREATED_DATETIME
    FROM OV80STG.PRCSD_DATA_OUTPUT_ARCHIVE
    WHERE MODEL_CODE = 'MDLADV1538'
          AND TAG_VAL <> 'U_Transfer_rate'
) P
WHERE(P.TAG_VALUE > 100);

ORA-01722: invalid number error

3 Answers