• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 70
  • Last Modified:

SQL Syntax Error

I'm getting the following error ... thoughts

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number  
Select distinct TO_CHAR(coalesce(C_DECISION_LETTER_MAILED_DT,C_DECISION_LETTER_MAILED_DT),'MM-DD-YYYY')as "Decision Lt Mail Dt" from T_MAS_APL
0
shieldsco
Asked:
shieldsco
2 Solutions
 
Jan LouwerensSoftware EngineerCommented:
What are the datatypes of the C_DECISION_LETTER_MAILED_DT and C_DECISION_LETTER_MAILED_DT fields? Perhaps they need to be converted to some kind of date/time datatypes first?
0
 
Bill PrewCommented:
Sounds like one of these has some data in it that isn't numeric and can't be converted to a date.

C_DECISION_LETTER_MAILED_DT
C_DECISION_LETTER_MAILED_DT


»bp
0
 
slightwv (䄆 Netminder) Commented:
I agree with the above posts:  One or both of those columns isn't a DATE data type.

Oracle will try and do an implicit data type conversion.  For dates that defaults to the format DD-MON-YY.  It then goes by the NLS_DATE_FORMAT setting.  If none of those match the string data, you can get the ORA-01722.

This is why you should NEVER store dates as strings.

Since you likely have, if you try to use TO_DATE before the TO_CHAR to change the format, you'll probably run into bad data and the TO_DATE will fail.  I would suggest using SUBSTR to change the format from one string format to another.  Another option is to create your own function and have some default date or null returned if you have bad data.

I also wanted to point out that you should ask Oracle Questions in the Oracle Database Topic Area for the fastest response.  I've added it for you.
0
 
shieldscoAuthor Commented:
Thanks I agree
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now