Date problem in QV10 working on previous version

[#1]

Hi,

I have a script loading from an oracle table and data load is limited by start date and end date. This script has been (and still is) working perfectly fine on Qlikview 8.20 (yeah I know, old version ). When transferring this to the newer QV10 server, I get "ORA-01861: literal does not match format string" error. It is the very same script, and I can cope it from the newer server to the old one, and the script works perfect.

Any ideas what I can do to solve this?

Let StartDate = date('2010-01-01','YYYY-MM-DD');
Let EndDate = date(today(),'YYYY-MM-DD');

tmpLoad:
LOAD
"TRANSACTION_DATE";
SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE

WHERE("TRANSACTION_DATE">= '$(StartDate)'
AND "TRANSACTION_DATE"< '$(EndDate)' );

BR

Robert

[#2]

Hi Robert,

This Oracle issue is due to parsing error in your script. The StartDate and EndDate format type is not matching to Oracles. I am not sure why this is occuring in one version but not the other. If you generate log (document properties-> Generate Log file) in both the versions we can compare the SQLs they are passing to Oracle and find the difference.

Its always best to user SQL without custom formats. The following code will work if your QVS and Oracle are in the same timezone. If not incorporate the difference.

Let DateDiff = num(date(today(),'YYYY-MM-DD')-date('2010-01-01','YYYY-MM-DD'));

tmpLoad:
LOAD
"TRANSACTION_DATE";
SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE
WHERE("TRANSACTION_DATE">= sysdate-'$(DateDiff)'
AND "TRANSACTION_DATE"< sysdate );

Hope this helps,

Kiran.

[#3]

Hi,

these are the log files, first from the old server, second is from the new server

2011-10-25 11:21:27: 0015  Let StartDate = date('2010-01-01','YYYY-MM-DD')
2011-10-25 11:21:27: 0016  Let EndDate = date(today(),'YYYY-MM-DD')
2011-10-25 11:21:27: 0019  tmpLoad:
2011-10-25 11:21:27: 0020  LOAD 
2011-10-25 11:21:27: 0021  "TRANSACTION_DATE"
2011-10-25 11:21:27: 0022  SQL SELECT 
2011-10-25 11:21:27: 0023  "TRANSACTION_DATE"
2011-10-25 11:21:27: 0024  FROM MY_TABLE
2011-10-25 11:21:27: 0025  WHERE("TRANSACTION_DATE">= '2010-01-01'
2011-10-25 11:21:27: 0026  AND "TRANSACTION_DATE"< '2011-10-25'
2011-10-25 11:21:27: 0027  )
2011-10-25 11:21:30:        1 fields found: TRANSACTION_DATE, 10 lines fetched
2011-10-25 11:21:33:      Execution finished.
-----------------------------------------------------------------------------------------
2011-10-25 11:14:26: 0015  Let StartDate = date('2010-01-01','YYYY-MM-DD')
2011-10-25 11:14:26: 0016  Let EndDate = date(today(),'YYYY-MM-DD')
2011-10-25 11:14:26: 0020  tmpLoad:
2011-10-25 11:14:26: 0021  LOAD
2011-10-25 11:14:26: 0022  "TRANSACTION_DATE"
2011-10-25 11:14:26: 0023  SQL SELECT 
2011-10-25 11:14:26: 0024  "TRANSACTION_DATE"
2011-10-25 11:14:26: 0025  FROM MY_TABLE
2011-10-25 11:14:26: 0026  WHERE("TRANSACTION_DATE">= '2010-01-01'
2011-10-25 11:14:26: 0027  AND "TRANSACTION_DATE"< '2011-10-25')
2011-10-25 11:14:27:       Error: SQL##f - SqlState: S1000, ErrorCode: 1861, ErrorMsg: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string
2011-10-25 11:14:30:      Execution finished.

Howerver, the script you posted seems to work perfectly fine. I will incorporate that with the full load and match the results from the different servers.

BR

Robert

[#4]

ok let me know if it work

[#5]

Hi,

when validating the full load from 2010 untill today, I get the exact same valuse on the old and the new server. Many thanks for the help!

The script used on the new server is now

tmpLoad:

Let DateDiff = num(date(today(),'YYYY-MM-DD')-date('2010-01-01','YYYY-MM-DD'));

tmpLoad:

LOAD

"TRANSACTION_DATE";

SQL SELECT

"TRANSACTION_DATE"

FROM MY_TABLE

WHERE("TRANSACTION_DATE">= sysdate-'$(DateDiff)'

AND "TRANSACTION_DATE"< sysdate );

The only "problem" that still persists is that sysdate refers to a time stamp. The applicatione is created so that a data load is extracting data to qvd-files every night with data between start date and end date. The data for current date is updated in another application via event driven load. On the previous server I was limiting the nightly data extract to midnight the day before, for instance 24th of October, and the data for 25th of October was loaded by the user at any time during the day via the event. If I load data until sysdate and then update with data for current date, I will eventually get double data for current date, both from QVD and from direct database load at event.

Is there any way to truncate sysdate to let the load stop at midnight?

BR

Robert

[Miguel_Angel_Baeyens]

Hi Robert,

Try the following in your WHERE statement in the SQL to Oracle

WHERE TO_CHAR(TRANSACTION_DATE, 'YYYY-MM-DD') >= '2010-01-01'
AND TO_CHAR(TRANSACTION_DATE, 'YYYY-MM-DD') < '$(=Date(Today(), 'YYYY-MM-DD'))';

You can LET the Date(Today()) part in a variable if that suits you.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

Hi,

Use the following in your second condition instead of sysdate. It flags off data at midnight.

decode(to_char(sysdate,'HH24'),'01',0,sysdate)

Best Regards,

Kiran.

[#8]

Hi

I'm sorry for bringing this up again, but I haven't had the time the past week.

My WHERE condition now says

WHERE("TRANSACTION_DATE">= sysdate-'$(DateDiff)'
AND "TRANSACTION_DATE"< decode(to_char(sysdate,'HH24'),'01',0,sysdate) 

This now ends up in error message "ORA-00932: inconsistent datatypes: expected NUMBER got DATE"

Any ideas?

BR

Robert

[#9 · Accepted solution]

Can you try

"TRANSACTION_DATE"<=decode(to_char(sysdate,'HH24'),'01',TRUNC(sysdate)-1,TRUNC(sysdate))

Regards,

Kiran.

[#10]

Hi

that seems to work like a charm, many thanks for your help!

[#11]

Robert,

I didn't validate the logic so be vigilant while implementing.

Thanks,

Kiran.