Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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');
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
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
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