SQL where clause causing reload error

[#1]

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?

[#2]

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

[pat_agen]

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.

[#4]

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

[pat_agen]

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?

[#6]

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.

[#7]

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

[pat_agen]

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.

[#9]

Hi,

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

In SQL statement the sintaxe UNION not work for me.

[#10]

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!