Having issues with using a variable in my where clause?

[#1]

I'm creating a 3 part incremental script - Step 1 Extract Script, Step 2 Transform, Step 3 Append new set on QVD. 

I'm having an issue with my variables in Step 1.  I believe it's because my date field is a data time field. 

I have created an application that has 2 calendar prompts, Start Date and End Date, users select the Start and End Date and hit a reload button. 

In the Extract Script I have the following information: (I'm pairing it down in this example)

SQL

SELECT

  access_time

  ,to_char(access_time, 'YYYY-mm-DD') as access_date

FROM pixel_table

where to_char(access_time, 'YYYY-mm-DD')  >= $(vStart)

AND   access_time  <= $(vEnd)

When I run my program - it gives me the error that it can't find column YYYY. 

I want this to be interactive so we can select the dates from the calendar object and it is passed through to the vStart and vEnd. 

What am I doing wrong?  I'm attaching my QVW.  It won't run, but maybe you can see something.  The format of the access_time field is YYYY-MM-DD 00:00:00. 

I don't have to script the time off in the where statement, but I can't figure out how to handle the time stamp portion on this.  I tried both ways and if I hard code a date in there then it works, so it's an issue with how I set up the variable. 

[#2]

Hi I have an update, when I try changing the char to a cast and hardcode the dates, it works!!!  Then next issue is when I try to add my variables vStart and vEnd back into the program.   It doesn't fail, but it returns 0 lines fetched. 

I'm thinking there is another disconnect with the data type between cast(access_time as date) and the variables I created, but I don't know how to prove it.

Updated QVW attached.

[Digvijay_Singh]

One option can be to use where clause in Qlikview load statement instead of select statement which will be executed by source database, won't give better performance but we will have better control of formatting.

[#4]

Hi Digvijay,

The reason why I'm hitting the database is because this is part of my extraction step and how I plan on limiting the data.   It's a very large table.

I did find a solution to my problem which seems a little extensive but it works.  Basically I'm breaking down the variable from an integer to a string, and recasting it as a date as I parse out the Year-Month-Day.

where cast(access_time as date) >= cast( substring( cast ( $(vStart) as varchar ), 1, 4) + '-' + substring( cast ( $(vStart) as varchar ), 5, 2) + '-' + substring( cast ( $(vStart) as varchar ), 7, 2) as date )
and
cast(access_time as date) <= cast( substring( cast ( $(vEnd) as varchar ), 1, 4) + '-' + substring( cast ( $(vEnd) as varchar ), 5, 2) + '-' + substring( cast ( $(vEnd) as varchar ), 7, 2) as date )

I'm not sure if this is the most efficient way to do this, but it works.

[vishsaggi]

can you try any one of below scripts and let us know ?

LOAD *

WHERE Access_Time  >= '$(vStart)'

AND   Access_Time  <= '$(vEnd)' ;

LOAD Date(Date#(access_time, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS Access_Time,

          Amount;

SQL

SELECT

  access_time,

    amount

FROM affiliatebi.public.ap_pixel_log;

--*****************************************************************************************

The above script OR the below

--*****************************************************************************************

LOAD *;

SQL

SELECT

  access_time

  ,to_char(access_time, 'YYYY-mm-DD') as access_date

FROM pixel_table

where to_char(access_time, 'YYYY-mm-DD')  >= '$(vStart)'

AND   to_char(access_time, 'YYYY-mm-DD') <= '$(vEnd)'

[#6 · Accepted solution]

Hi Jodi,

This should give you what you need and it is a little easier to follow:

Let vStart2 = date(date#($(vStart),'YYYYMMDD'),'YYYY-MM-DD');
Let vEnd2 = date(date#($(vEnd),'YYYYMMDD'),'YYYY-MM-DD');

SQL
SELECT
access_time
....
FROM affiliatebi.public.ap_pixel_log

where cast(access_time as date) >= cast( '$(vStart2)' as date )
    and cast(access_time as date) <= cast( '$(vEnd2)' as date );

I tried it out in a test SQL Server database and it appears to be working fine.

Hope this works!

- Stan

[#7]

Hi Stan! We have a winner!  This worked!! Thank you!!  It is a much simpler version then what I came up with.  I will be using this solution in my program.  Thank you everyone!  

[#8]

Awesome - glad it worked!