Loading Date from Oracle DB to QlikView

[abhaytrip]

Hi ,

I've got an issue where I want to load date from MS Access to QlikView but there seems to be this error coming up.

Error : SQL##f - SqlState: S1000, ErrorCode: 1843, ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month

The date that i'm loading from MS-Access is this format .

The Query that I've Written on QlikView in order to get the data only for that particular day.

But the error i've been facing is not understandable to me as I'm new to QlikView.

Any sort of help is appreciated.

Thanks in advance.

Abhay.

[abhaytrip]

Sorry , 

I forgot to add but I did change the TT in Snapshot to AM/PM .

But landed with no results.

[Peter_Cammaert]

Most RDBMS natively support and understand dates in database format, e.g. 'YYYY-MM-DD'. Did you try formatting your MS-Access dates in this way, before delivering them as a filter to Oracle?

[abhaytrip]

Nope ,

I cant touch the MS-Access or Oracle DB as its from the client side.

All I can try is to make changes on the qlik side so as to meet the desired requirement .

Any suggestions on the Qlik side , that I can do ?

[Peter_Cammaert]

I understand that, but nobody is asking you to change anything in your databases. The formatting can be done in QlikView.

Imagine storing the date value you need to pass to Oracle in a variable called vSelectionDate. You're not telling what your MS-Access/Oracle source field is, so let's assume you have another variable that contains the MS-Access/Oracle date value. A statement like this will convert the text representation into database format:

LET vSelectionDate = Date(vMSAccessDate, 'YYYY-MM-DD');

and your Oracle statement can become:

SQL SELECT *

FROM "Database_Name.Table_Name"

WHERE "Date Field" = '$(vSelectionDate)';

Only if "Date Field" is the name of an Oracle table column, of course.

Note that date values in MS-Access used to require pound signs at the front and end. But I'm not sure anymore I understand which DB product is where...

[Anil_Babu_Samineni]

Peter, One quick question, I haven't done testing correct me if i am wrong.

LET is Qlik Functionality and We are using the QV functionality with in SQL. Does this works? Because few SQL functionality not working in Qlik environment. Reply me if you have time

[maxgro · Accepted solution]

if you have Oracle,

you can use an Oracle function to convert a string to a date

where oracledatefield >= to_date('20012018 05:00:00', 'DDMMYYYY HH24:MI:SS')

or if you only need a date

where oracledatefield >= to_date('20012018', 'DDMMYYYY')

Oracle to_date tips

[abhaytrip]

This works , Thanks a lot !

[abhaytrip]

This works as well , Thank you so much !

[Peter_Cammaert]

Every piece of code I included in my post is QlikView Script code, to be used in a LOAD script. $-sign substitution is used to transfer QlikView values into SQL statements before they are transferred to an RDBMS. I don't see why this wouldn't work.

PS There really is no other method to include a QlikView-created value as a parameter or a clause (or any other statement part) in a SQL statement. Moreover, if that wouldn't be possible, we would have to hard-code every variation of a SQL statement in our load scripts.