topic Re: From date and To Date filter in load script where condition Issue in QlikView

From date and To Date filter in load script where condition Issue

Anonymous — Mon, 21 Jan 2013 18:14:27 GMT

business_date column has following data format: '2011-01-31 00:00:00'

//Filtering records from date and to date first approach

LET vStartDate = $(vYear)&'-'&$(vMonthCount)&'-01 00:00:00';

LET vEndDate = $(vYear)&'-'&$(vMonthCount)&'-31 00:00:00';

// Month level QVD data retrieval

$(vQVDFileName):

SQL SELECT *

// FROM

FROM DataSource.dbo."SourceTable"

WHERE Date(business_date,'YYYY-MM-DD') >= {d' $(vStartDate)'}

AND Date(business_date,'YYYY-MM-DD') <= {d' $(vEndDate)'};

Error Message:ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'Date' is not a recognized built-in function name

Tried with only right hand side conversion second approach:

SQL SELECT *

// FROM "Client_Sales_Assets_Schema"."client_asset_v"

FROM DataSource.dbo."SourceTable"

WHERE business_date >= DATE#($(vStartDate),'YYYY-MM-DD hh:mm:ss') AND business_date <= DATE#($(vEndDate),'YYYY-MM-DD hh:mm:ss');

Error message :ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near '00'.

Any thoughts how to implement from and to date filter when I am having data in source table as '2011-01-31 00:00:00'?.

Thanks

Dasu.G

Re: From date and To Date filter in load script where condition Issue

danielrozental — Tue, 22 Jan 2013 00:25:18 GMT

You can't use qlikview functions in the ODBC query.

//This part gets executed in QlikView, hence you can use QlikView functions

LOAD

something,

something_else

//This part gets executed in SQL SERVER through the ODBC, hence you need to use SQL Server functions.

SQL

SELECT * FROM TABLE WHERE SQLSERVERFUNCTION(something);

Try something like

Let vCutDate = date(Today(),'MM/dd/yyyy');

LOAD *;

SQL SELECT *

FROM blue.dbo.iApplicationStat

WHERE Timestamp >= convert(datetime,'$(vCutDate)',101);

Or check the SQL Server convert function online

Re: From date and To Date filter in load script where condition Issue

Anonymous — Thu, 24 Jan 2013 15:08:38 GMT

Hi ,

I tried with SQL server it worked well:

MS SQL Data Source:

SQL SELECT *
FROM "Schema"."c_a_v"

WHERE business_date >= convert(DATETIME,'2008-01-01 00:00:00',101)
AND business_date <= convert(DATETIME,''2008-01-31 00:00:00',101);

Executing very well and i am able to proceed.

ORACLE Data Source:

SQL SELECT *

FROM "Schema"."c_a_v"

WHERE business_date >= TO_DATE('2008-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

AND business_date <= TO_DATE('2008-01-31 00:00:00','YYYY-MM-DD HH24:MI:SS')

Error Message:

SQL##f - SqlState: HY000, ErrorCode: 1000, ErrorMsg: Unable to parse query text: Incorrect syntax near "AND",

Note : when I run these queries in actual source it is exectuing fine.

Re: From date and To Date filter in load script where condition Issue

hic — Thu, 24 Jan 2013 16:43:25 GMT

A small comment on how QlikView handles SELECT statements: QlikView first expands the variables in the $-expansions (see e.g. Daniel Rozentals where-clause that contains a reference to vCutDate - which is a very good way of setting the where clause in a SELECT). Then QlikView sends the entire SELECT statement as a string to the ODBC for evaluation and waits for a response.

So if your query runs fine in your normal ORACLE client but not in QlikView, it means that the ODBC driver you use interprets the SELECT differently from your normal ORACLE client. It is not a QlikView bug.

HIC

Re: From date and To Date filter in load script where condition Issue

Anonymous — Thu, 24 Jan 2013 21:57:07 GMT

Henric,

Probably it could be a ODBC driver issue. But when I am executing below script from QLIKVIEW it always giving me error on second sql select execution.

I commented first sql then it is failing in 3rd sql likewise. If i comment second one then it is giving script error for 3rd sql statement. Looks like it is always executing only one SQL Select. any thoughts
?.

ODBC CONNECT TO OracleSource;

Sales_2008_1:

SQL SELECT *

FROM "SalesSchema"."s_v"

WHERE business_date >= TO_DATE('2008-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

AND business_date <= TO_DATE('2008-01-28 00:00:00','YYYY-MM-DD HH24:MI:SS');

Sales_2008_2:

SQL SELECT *

FROM "SalesSchema"."s_v"

WHERE business_date >= TO_DATE('2008-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

AND business_date <= TO_DATE('2008-02-28 00:00:00','YYYY-MM-DD HH24:MI:SS');

Sales_2008_3:

SQL SELECT *

FROM "SalesSchema"."s_v"

WHERE business_date >= TO_DATE('2008-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

AND business_date <= TO_DATE('2008-03-28 00:00:00','YYYY-MM-DD HH24:MI:SS');

Re: From date and To Date filter in load script where condition Issue

Anonymous — Fri, 25 Jan 2013 00:07:30 GMT

event viewer logs are mentiond below along with the screens:

Event Viewer :

Faulting application name: QvConnect32.EXE, version: 11.0.11414.0, time stamp: 0x50647b2f

Faulting module name: ntdll.dll, version: 6.1.7601.17725, time stamp: 0x4ec49b8f

Exception code: 0xc0000374

Fault offset: 0x000ce6c3

Faulting process id: 0x1bb4

Faulting application start time: 0x01cdfa8f06619989

Faulting application path: C:\Program Files (x86)\QlikView\QvConnect32.EXE

Faulting module path: C:\Windows\SysWOW64\ntdll.dll

Report Id: 46d2df61-6682-11e2-bd50-005056a13490

Re: From date and To Date filter in load script where condition Issue

hic — Fri, 25 Jan 2013 10:01:13 GMT

It seems as if the ODBC driver after the first SELECT comes in a state where it cannot execute second...

* Is the driver made by ORACLE, or is it third party? Both MSFT and DataDirect produce ORACLE drivers. Try another driver.

* You can also try to disconnect after each SELECT:

CONNECT ... ;

SELECT ... ;

DISCONNECT;

CONNECT ... ;

SELECT ...

DISCONNECT ;

etc

HIC

Re: From date and To Date filter in load script where condition Issue

avastani — Tue, 29 Jan 2013 15:26:45 GMT

You could also put your statements which seem to differ by where condition date through a for loop and do the connect disconnect. place the connect statement in a variable and call the variable each time to connect