topic SQL where clause causing reload error in QlikView

SQL where clause causing reload error

Thu, 16 Jun 2011 10:40:36 GMT

Hi,

I have an ODBC connection to a SAS dataset which loads successfully. I am now trying to filter the dataset on a date field in the SQL load script but cannot get this to work. I have the following:

SET DateFormat='DD/MM/YYYY';

Let MonthStart = date(MonthStart(AddMonths(Today(),-1)),'DD/MM/YYYY');

Let MonthEnd = date(MonthEnd(AddMonths(Today(),-1)),'DD/MM/YYYY');

ODBC CONNECT TO <SAS Database>;

TEST:

LOAD DATE,

VALUE

SQL SELECT *

FROM DATABASE.DATASET

WHERE DATE BETWEEN $(MonthStart) AND $(MonthEnd);

I'm assuming there is a formatting issue between the date field being read in from SAS and my comparison variable. I've tried applying the SQL DATEPART and CONVERT functions to try to force the date into a comparable format but am getting no where.

Any help would be much appreciated.

Thanks

Gordon

PS: I also got a script error trying to specific fields in the select statement, i.e. "SQL SELECT DATE, VALUE....". Is this a bug?

SQL where clause causing reload error

Thu, 16 Jun 2011 11:16:33 GMT

Hi,

I think the problem may be some missing '' symbols when Qlikview obtains the value from the variable. For solving this, when constructing MonthStart and MonthEnd concatenate them in this way:

MonthStart = Chr(39) & Date & Chr(39)

Anyway, use qlikview's debugger to check the error. Use a breakpoint when launching the query,copy the query qlikviews constructs in the debugger and try it inside an sql client.

Regards

SQL where clause causing reload error

pat_agen — Thu, 16 Jun 2011 11:20:59 GMT

hi,

are you not missing a semi colon between your Load statement and your sql select statement?

I would try with simple quotes around your variables. They contain values like 01/05/2011 and this might be causing problems.

I would cast either the sql date to a string or the variables to dates like this, (I also don't use keywords like between in sql queries that are going through and odbc driver):

where DATE >= to_date('$(MonthStart)','DD/MM/YYYY')

and DATE <= to_date('$(MonthEnd)','DD/MM/YYYY')

hope this helps.

SQL where clause causing reload error

Thu, 16 Jun 2011 11:41:08 GMT

Hi,

Thanks for your responses.

I've tried both of your suggestions and I'm still getting the same error. It's frustrating that the error is not specific. I've also tried looking in the debugger but don't see anything that gives any insight.

pat.agen - yes, you're right that my example above is missing a semi-colon. I typed out the code as couldn't copy and paste and didn't know how to add code. I have the semi colon in my actual code.

Thanks

Gordon

SQL where clause causing reload error

pat_agen — Thu, 16 Jun 2011 11:54:43 GMT

what error are you getting?

is it an odbc error?, or are you just not getting any lines back?

can you connect and recover data from the database without your where clause?

if so can you recover the DATE field and see what the values look like inside it? how does qv see them?

SQL where clause causing reload error

Thu, 16 Jun 2011 12:00:43 GMT

The only error I get is displayed as ...

SQL Error: SQL Scriptlive: SQL State: 00000

<SQL statement printed>

My SQL statement loads the data when there is no where clause, but I don't know how to display the date as Qlikview sees it because once it is loaded it is displayed as DD/MM/YYYY.

SQL where clause causing reload error

Thu, 16 Jun 2011 12:03:13 GMT

Sorry, error should state "Scriptline" and not "Scriptlive".

SQL where clause causing reload error

pat_agen — Thu, 16 Jun 2011 12:09:48 GMT

and this is irrespective of how you have coded the sql "where" clause?

have you tried harcoding a date just to test that you can actually write a where clause aginst your DATE field?

get that to work and then get on to a substitution with a variable as you will nwo knwo the format you need to present your variable in.

SQL where clause causing reload error

Thu, 16 Jun 2011 12:22:41 GMT

Hi,

Try change "Between" to ">=" and "<=", maybe the sql native not undersand "Between".

In SQL statement the sintaxe UNION not work for me.

SQL where clause causing reload error

Thu, 16 Jun 2011 12:29:42 GMT

I've tried the following where clauses and all produce the same error...

Where DATE >= '01/05/2011'

and DATE <= '31/05/2011'

Where DATE >= date('01/05/2011','DD/MM/YYYY')

and DATE <= date('31/05/2011','DD/MM/YYYY')

Where date(DATE,'DD/MM/YYYY') >= date('01/05/2011','DD/MM/YYYY')

and date(DATE,'DD/MM/YYYY') <= date('31/05/2011','DD/MM/YYYY')

..sorry, don't know if we are going to get to the bottom of this!

SQL where clause causing reload error

pat_agen — Thu, 16 Jun 2011 12:36:36 GMT

hi Gordon,

yes I am going to have to give up soon.

One last try is your cast to date

date('01/05/2011','DD/MM/YYYY')

that should be

to_date('01/05/2011','DD/MM/YYYY')

or at least it is when using oracle sql

only cast one side so you either do

where DATE >= to_date('01/05/2011','DD/MM/YYYY')

where to_char(DATE,'DD/MM/YYYY') >= '01/05/2011'

we've gone too far to stop now!

SQL where clause causing reload error

Thu, 16 Jun 2011 12:43:20 GMT

Thanks for your help. Unfortunately I ready to give up right now!

We may be having a Qlikview rep here soon(ish!) so I might try to pick their brain.

Thanks

Gordon

SQL where clause causing reload error

Thu, 16 Jun 2011 12:48:54 GMT

Does you exclude the filter "Where..." the statement return any information?

SQL where clause causing reload error

Thu, 16 Jun 2011 12:52:04 GMT

I have successfully executed a where clause on the VALUE field, but it seems to be that the DATE field is in an unknown format and therefore generating a script error.

SQL where clause causing reload error

Thu, 16 Jun 2011 13:13:02 GMT

You can filter your table after load SQL too

TEST_TMP:

LOAD DATE,

VALUE

SQL SELECT *

FROM DATABASE.DATASET

WHERE DATE BETWEEN $(MonthStart) AND $(MonthEnd);

LOAD DATE, VALUES

RESIDENT TEST_TMP

WHERE DATE >= '$(MonthStart)'

AND DATE <= '$(MonthEnd)'

I think that problem is a datatype of this field in table. Does your SQL statement run in database?

SQL where clause causing reload error

Fri, 17 Jun 2011 10:21:05 GMT

Hi,

Just to let you know I got this to work. It seems that my field name DATE was a reserved word and therefore caused an error on reload. When I changed my field name to EXTRACT_DATE and converted the Qlikview date number to a SAS date number this worked. Code as follows:

ODBC CONNECT TO <SAS DATASET>;

Let MonthsFromToday = -1;

Let SASDateConvert = 21916;

Let MonthStart = num(date(MonthStart(AddMonths(Today(),$(MonthsFromToday),'DD/MM/YYYY')))) - $(SASDateConvert);

Let MonthEnd = num(date(MonthEnd(AddMonths(Today(),$(MonthsFromToday),'DD/MM/YYYY')))) - $(SASDateConvert);

TEST:

LOAD EXTRACT_DATE,

VALUE;

SQL SELECT *

FROM DATABASE.DATASET

Where EXTRACT_DATE >= $(MonthStart)

and EXTRACT_DATE <= $(MonthEnd);