CODEWITHSUNDEEP
oracle-database
kristian
kristian

Reputation: 3

ORA-01722: invalid number but datatype is date and it will not accept it in any way

Yet another of these issues of not being numeric, just can't figure it out.

My query:

SELECT
    s.user_id,
    date_time, 
    to_char(Date_time, 'DD-MON-YYYY HH24:MI:SS') timestamp,
        ( SELECT DECODE
        FROM (  SELECT  DECODE, code
                FROM    codelist_decode
                WHERE   codelist_id = 5029
                AND language_code = 001
             ) t5
        WHERE  t5.code = s.operation_type) 
    AS operation,
        ( SELECT t1.module_name_internal
        FROM (  SELECT  module_name_internal, module_id
                FROM    aris_admin_module
              ) t1
        WHERE  s.module_id = t1.module_id
        ) 
    AS module,
    t.session_id,
    t.seq_admin_audit_trail,
    t.record_id,
        ( SELECT t4.field_name_e
          FROM (  SELECT  field_name_e, admin_field_id
                  FROM    aris_admin_table_field
                ) t4
          WHERE admin_field_id = t.field_id) 
    AS field_name,
        ( SELECT t2.DECODE
          FROM  (  SELECT  DECODE, code
                   FROM    aris_admin_audit_decode
                   WHERE   field_id = t.field_id
                ) t2
          WHERE  t2.code = to_number(t.old_value)) 
    AS org_value_yn,
    t.old_value,
        ( SELECT t3.DECODE
          FROM ( SELECT  DECODE, code
                 FROM    aris_admin_audit_decode
                 WHERE   field_id = t.field_id
                ) t3
          WHERE t3.code = to_number(t.new_value)) 
    AS new_value_yn
    ,t.new_value
FROM
    system_admin_audit_session   s,
    system_admin_audit_trail     t
WHERE s.session_id = t.session_id
--order by TO_NUMBER('s.session_id','9999999999')
--or order by to_date(s.date_time) 
order by 2

The above generates the error:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

I've tried on ordering on several other columns but same issue.

If I simplify the query it works fine:

select s.user_id,s.date_time
from ARISG_PROD.system_admin_audit_session s
order by 2 desc

So why is it not recognizing my datetime as date and accepts the ordering?

Upvotes: 0

Views: 1863

Answers (1)

ekochergin
ekochergin

Reputation: 4129

Let's see if I can help you here. Normally, such an error can occur when a string is being compared to a number.

So, foe example if s.module_id was a varchar2 and t1.module_id was defined as a number, the statement like this

s.module_id = t1.module_id

could be the root cause.

I'd recomment you to comment out all the subqueries in the main select statement and try the query whether the error occurs.

Once the error is gone you have to analyze the comparison statement. Or post it here and we'll have a look

Upvotes: 1

Related Questions