Upload Datatype conversion issues - Qlik Community

snehamahaveer · ‎2015-08-28

Hi Everyone,

I am working on a dashboard which uses SQL upload for its data.

The problem I am facing is that when I use to_number() function to convert a Month from date and time field to number it gives a ORA 01481 error - invalid number format.

This is my SQL upload statement,

vat.EVTDATE AS EVENT,

to_char(vat.EVTDATE,'MON' ) as Month_Activity,

to_char(vat.EVTDATE,'(YYYY: DD,MON)' ) as MonthDay_Activity,

to_char(vat.EVTDATE,'MM') as Month_Activity_Num,

It works well with the to_char function and an error occurs only when I use to_number(). Where am I going wrong.?

to_number(vat.EVTDATE,'MM') as MM_Activity_Num,

Your help is much appreciated.

jonathandienst · ‎2015-08-28

This is more an Oracle question than a QV question. Perhaps you should post it in an Oracle forum.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

PrashantSangle · ‎2015-08-28

Hi,

I agree with Jonathan, It is not QlikView issue.

Find below link might be helpful.

dba.stackexchange.com/questions/4941/oracle-format-specifiers-to-number-vs-to-char

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Ralf-Narfeldt · ‎2015-08-28

You could read the value raw from Oracle, and do the processing in QlikView instead, using QlikView functions in a preceding load.