topic Re: SQL Query Error in QlikView

SQL Query Error

techvarun — Fri, 06 Sep 2013 09:20:53 GMT

Hi Folks,

I am trying to Load Conditional data based on fromdate and todate from SQL server through DatePicker Object.

But when try to load the script

"SQL SELECT * FROM Persons WHERE DOB Between 'date(date#(99999999 - $(StartDate),'YYYYMMDD'))' and 'date(date#(99999999 - $(EndDate),'YYYYMMDD'))';"

it is showing the following error

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

SQL SELECT * FROM Persons WHERE DOB Between 'date(date#(99999999 - 41082,'YYYYMMDD'))' and 'date(date#(99999999 - 41083,'YYYYMMDD'))'

Please help me to sort out the error in the SQL Query

Thanks in Advance

Re: SQL Query Error

tresB — Fri, 06 Sep 2013 09:46:55 GMT

Hello Varun,

QV functions can't be used under SQL command. You have to use some SQL date function. Otherwise you can load data into qv once then try to filter using qv functions like:

Load * Where Date(.....)>'....';

SQL Select * from <DB>;

Hope this helps

Re: SQL Query Error

hic — Fri, 06 Sep 2013 09:47:35 GMT

The SELECT statement is evaluated by the OLE DB provider, not by QlikView. So, the QlikView Date() and Date#() functions are not available.

HIC

Re: SQL Query Error

swuehl — Fri, 06 Sep 2013 09:48:12 GMT

Whatever you write after the SQL SELECT will be sent to the DBMS and will not interpreted by QV (with the exception below).

You are using functions like date() that are not known by the DBMS (and the sequence of single quotes is also problematic here).

I am not sure what you want to achieve, but you can try to create two variables in your QV script (before the LOAD / SQL Select statement) that will evaluate your two dates and then use dollar sign expansion to include the values in your SELECT sent to the DMBS.

A variable dollar sign expansion is executed before the string is sent.

Re: SQL Query Error

techvarun — Fri, 06 Sep 2013 09:54:37 GMT

Tried this and now working fine.

LET VDate1 = Date($(StartDate),'YYYY-MM-DD');

LET VDate2 = Date($(EndDate),'YYYY-MM-DD');

SQL SELECT * FROM Persons WHERE DOB Between '$(VDate1)' and '$(VDate2)';

Regards

Re: SQL Query Error

techvarun — Fri, 06 Sep 2013 09:55:21 GMT

Thank you all for the help

Regards

Re: SQL Query Error

techvarun — Fri, 06 Sep 2013 09:55:46 GMT

Thanks HIC

Re: SQL Query Error

er_mohit — Fri, 06 Sep 2013 09:59:30 GMT

Try this

load * where DOB>=$(StartDate) and DOB<=$(EndDate);

"SQL SELECT * FROM Persons;