Re: ORA-01722 invalid number error & subquery

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 23 Apr 2003 07:34:59 -0700
Message-ID: <92eeeff0.0304230634.7b2e715f_at_posting.google.com>


sawmyat_aung_at_yahoo.com (sma) wrote in message news:<b13ca17.0304221919.30b59a90_at_posting.google.com>...
> I got ORA-01722 invalid number error in running this SQL statement
> -------------------------------------------------
> 1. SELECT COUNT(T4.bbb_no) , T1.t1_code
> 2. FROM table2 T2, table1 T1, Table4 T4, table5 T5, table6 T6
> 3. WHERE T4.aaa_no != 0 AND T4.bbb_no = T2.bbb_no AND T2.aaa_no = T2.ccc_no
> 4. AND T2.aaa_no = T1.aaa_no
> 5. AND T4.aaa_no = T5.aaa_no AND T5.aaa_name = 'A003'
> 6. AND TO_CHAR(TO_DATE(T4.aaa_date,'YYYYMMDD') + T5.aaa_days -1,'YYYYMM')
> 7. = '200304'
> 8. AND T4.ddd_code = 91 AND T4.eee_code = 10
> 9. AND T1.aaa_code in (select aaa_code from AAA_BBB where BBB_code = 1)
> 10. AND T4.ddd_code = T6.ddd_code(+) AND T4.eee_code = T6.eee_code(+)
> 11. AND T4.aaa_no = T6.aaa_no(+) AND T4.aaa_date = T6.aaa_date(+)
> 12. AND TO_CHAR(TO_DATE(T4.aaa_date,'YYYYMMDD') + T5.aaa_days -1,'YYYYMMDD') <
> 13. DECODE(T6.bbb_date, NULL, '99999999', T6.bbb_date)
> 14. GROUP BY T1.t1_code
> -------------------------------------------------
> Error occur in line 4 at the position of T1.aaa_no
> Its data type is char(2). But only stored numeric characters. It's sure.
>
> What I'm amazing is.......
> It work well
> - if I remove line 9 or
> - if I replace subquery with its equivalent expression list .
> Is there any relationship?

  1. Line 4 should not be a problem as long as both T2.aaa_no and T1.aaa_no are same datatype...Unless you use either column in arithmatic operation with non-numeric values.
  2. I suspect T5.aaa_days is a VARCHAR datatype with string value and you are using it in arithmatic operation. Check that.
  3. You don't need to have TO_DATE around aaa_date. You can write it as TO_CHAR(TRUNC(T4.aaa_date) + T5.aaa_days -1,'YYYYMM')

Regards
/Rauf Sarwar Received on Wed Apr 23 2003 - 16:34:59 CEST

Original text of this message