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?

[pat_agen]

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')

or

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

we've gone too far to stop now!

[#3]

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

[#4]

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

[#5]

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.

[#6]

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?

[#7 · Accepted solution]

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);