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. 

[vishsaggi]

You are putting all the date format types into your variables. So it is actually doing this

Where to_char(access_time, 'YYYY-mm-DD') = 'YYYY-MM-DD'

But your variables are holding these dates. like

vStart = 20160908

vend = 20160912

So you have t ochange the formatting. I have not tired but try this?

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

AND   access_time  <= $(vEnd)    -----> convert thsi to the access_time format.

[#3]

Your users enter their desired dates using the two calendar objects. The two variables vStart and vEnd will now have the desired values. All is good.

Then when the user hits the reload button, the values in the vStart and vEnd variables are lost because of the two SET commands on the Main tab of the script:

SET vStart= 'YYYY-MM-DD';
SET vEnd= 'YYYY-MM-DD';

So, the where clause on your load statement will be interpreted as follows:

FROM affiliatebi.public.ap_pixel_log
where to_char(access_time, 'YYYY-mm-DD') >= YYYY-MM-DD

That is why you are getting the error cannot find column YYYY.

If you remove the two SET commands this should fix this issue.

Hope this helps,

- Stan

[#4]

Thank you Vish for the quick response.  Now I receive a new error.  I definite agree with your asessment. I had a hunch I was happening. 

SQL##f - SqlState: S1000, ErrorCode: 30, ErrorMsg: [Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: ERROR:  multiple decimal points

RED_AP_PIXEL_LOG:

SQL

SELECT

       access_time

       , amount

FROM affiliatebi.public.ap_pixel_log

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

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

Just so I'm clear, here is some examples of access_time straight at the database:

2015-11-30 02:00:25

2015-11-30 02:00:27

When I go to Settings => Variable Overview = the variables do have values but they are seem to be numeric and other has a format of YYYYMMDD.   Should I use a SET or LET command? 

I am also not sure if my calendar objects that use vStartDate, vEndDate are correct.  How is  selection in the calendar object going to pass the value to the where clause vStart variable.  I think I'm not using the variables correctly either.

[#5]

Hi Stan! Thanks for the quick reply!!  I removed those SET commands and I'm receiving the new error I posted below.  "Error Multiple Decimal Points".  So it fixed seeing the formatting as YYYY as a column.  However, I'm not sure why it's saying multiple decimal points since I'm not using them. 

[#6]

What is the data type in the SQL server database for access_time? The reason I ask is the following solution is suggested when the data type is BIGINT:

to_char(TO_TIMESTAMP(access_time/ 1000), 'YYYY-mm-DD')

[#7]

Hi Stan,

This is actually hitting Amazon’s Redshifts Database (which is new to me too). Access_time is a “timestamp without time zone” data type.

[vishsaggi]

Did you try this?

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

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

[#9]

Can you try the suggested fix real quick? Maybe it will give us a hint if we are going in the right direction.

[#10]

Ok, when I try Vish's suggestion to remove the variables, I receive the same decimal error message.  I

SQL##f - SqlState: S1000, ErrorCode: 30, ErrorMsg: [Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: ERROR:  multiple decimal points

RED_AP_PIXEL_LOG:

SQL
SELECT
access_time
    , amount

FROM affiliatebi.public.ap_pixel_log

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

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

I'm attaching the updated/modified QVW.

Thank you so much for you help.  I can't wrap my head around dates and variables together!