topic Re: Loading Date from Oracle DB to QlikView in QlikView

Loading Date from Oracle DB to QlikView

abhaytrip — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Loading Date from MS Access to QlikView

marcus_sommer — Thu, 18 Jan 2018 11:00:47 GMT

Try it with: where "Date Field" = '1201/2017';

- Marcus

Re: Loading Date from MS Access to QlikView

abhaytrip — Thu, 18 Jan 2018 11:10:59 GMT

There's still an error which says

Literal does not match format string.

Re: Loading Date from MS Access to QlikView

marcus_sommer — Thu, 18 Jan 2018 11:16:45 GMT

Within "Date Field" = '1201/2017' is a typo and it should be "Date Field" = '12/01/2017'. Further I'm not sure if access accept the single-quotes or if it must be another quoting maybe with double-quotes like: "Date Field" = "12/01/2017".

- Marcus

Re: Loading Date from MS Access to QlikView

krishna20 — Thu, 18 Jan 2018 12:29:11 GMT

Can you please share screenshot or code that you have used in edit script with an error message.

Re: Loading Date from MS Access to QlikView

abhaytrip — Thu, 18 Jan 2018 12:29:43 GMT

ya , I figured the typo but it doesn't seem to be working out.

Re: Loading Date from MS Access to QlikView

abhaytrip — Thu, 18 Jan 2018 12:48:54 GMT

This is what is coming after I tried Marcus's Method.

Re: Loading Date from MS Access to QlikView

stabben23 — Thu, 18 Jan 2018 13:03:08 GMT

use another provider?

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=Z:\Spec.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False];

Re: Loading Date from MS Access to QlikView

stabben23 — Thu, 18 Jan 2018 13:23:18 GMT

Is it really MS Access, not ORACLE DB you try to Connect to?

where TO_DATE("Date Field") = '12/01/2017'; maybe help

Re: Loading Date from MS Access to QlikView

abhaytrip — Fri, 19 Jan 2018 08:55:48 GMT

I wasn't sure until my Lead confirmed it to me.

Its from ORACLE To QLIKVIEW.

I tried the above but its not working out.

So what I did based on a small condition I pulled out some data from the ORACLE DB to have a look at the format of the "DATE_FIELD".

So this is the date field that I have in my ORACLE DB,

But i'm just not sure how do I pull it into QLIK.

The query which I tried but hasn't worked out.

Also , I'll correct the Discussion Topic.

Re: Loading Date from Oracle DB to QlikView

abhaytrip — Fri, 19 Jan 2018 09:01:12 GMT

Sorry ,

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

But landed with no results.

Re: Loading Date from Oracle DB to QlikView

Peter_Cammaert — Fri, 19 Jan 2018 09:43:04 GMT

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?

Re: Loading Date from Oracle DB to QlikView

abhaytrip — Fri, 19 Jan 2018 12:31:48 GMT

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 ?

Re: Loading Date from Oracle DB to QlikView

Peter_Cammaert — Sat, 20 Jan 2018 14:47:28 GMT

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...

Re: Loading Date from Oracle DB to QlikView

Anil_Babu_Samineni — Sat, 20 Jan 2018 14:54:52 GMT

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

Re: Loading Date from Oracle DB to QlikView

maxgro — Sat, 20 Jan 2018 15:02:39 GMT

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

Re: Loading Date from Oracle DB to QlikView

abhaytrip — Sun, 21 Jan 2018 04:03:42 GMT

This works , Thanks a lot !

Re: Loading Date from Oracle DB to QlikView

abhaytrip — Sun, 21 Jan 2018 04:04:10 GMT

This works as well , Thank you so much !

Re: Loading Date from Oracle DB to QlikView

Peter_Cammaert — Sun, 21 Jan 2018 20:21:23 GMT

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.