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
shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.