Error: Garbage after statement

[#1]

I have a document with an incremental load.  I'm reading in a QVD and purging my data for anything older than three months. I then want to concatenate that data set to the current day's transactions.  For some reason I'm getting a rather unusual error after the statement and I can't figure out why.  I will say in the log file it shows shows the STORE TSRTRAN command executing successfully, but my QVD doesn't get updated and I get an error.  I've attached both the qvw and log file.  I appreciate any insight!

Matt

MaxDate:

LOAD 

     max(CRT_DT) as LastCRT_DT

FROM $(Lvl1QVDdirectory)TSRTRAN.qvd (qvd);

vLastLoadDate = chr(39) & date(peek('LastCRT_DT',-1,'MaxDate'),'YYYY-MM-DD') & chr(39);

DROP Table MaxDate;

vDailyLoadDate = chr(39) & date(Today(),'YYYY-MM-DD') & chr(39);

//Gather current run's dataset which will be appeneded to the master

//QVD.

TSRTRAN_tmp:

LOAD TRAN_ID,

     PRNT_TRAN_ID,

     SRV_ID,

     DOC_ID,

     CRT_TS,

     MOD_TS,

     ERR_TYP_ID,

     PHYS_SRVR_ID,

     STTS_ID,

     CUR_RETRIES_NUM,

     USR_ID,

     END_TS,

     BUS_STTS,

     CRT_DT

FROM $(Lvl1QVDdirectory)TSRTRAN.qvd (qvd)

where CRT_DT > $(vQVDPurgeDate);

Concatenate

SQL SELECT "TRAN_ID",

    "PRNT_TRAN_ID",

    "SRV_ID",

    "DOC_ID",

    "CRT_TS",

    "MOD_TS",

    "ERR_TYP_ID",

    "PHYS_SRVR_ID",

    "STTS_ID",

    "CUR_RETRIES_NUM",

    "USR_ID",

    "END_TS",

    "BUS_STTS",

    "CRT_DT"

FROM WMBTCH.TSRTRAN

where CRT_DT > $(vLastLoadDate) and CRT_DT < $(vDailyLoadDate);

STORE TSRTRAN_tmp into $(Lvl1QVDdirectory)TSRTRAN.qvd(qvd);

DROP Table TSRTRAN;

[maxgro]

maybe

in first load

5/30/2014 4:42:28 PM: 0103       CRT_DT

5/30/2014 4:42:28 PM: 0104  FROM ..\QVD\Extract\TSRTRAN.qvd (qvd)

5/30/2014 4:42:28 PM: 0105  where CRT_DT > ';

vQVDPurgeDate is not defined

[#3]

Thanks for the reply Massimo.  I have it set on my main tab

LET vQVDPurgeDate = chr(39) & today()-92 & chr(39);

It works when I run just that snippet.  Something odd is going on here.

[#4]

But your right it doesn't show up in the log.

[Anonymous]

When using date variables like this, I always put them in single quotes:

where CRT_DT > '$(vLastLoadDate)' and CRT_DT < '$(vDailyLoadDate)';

[#6]

Thanks Michael - I'll try that out next.  This is error stands between me and my vacation so I appreciate all suggestions right now.  Do the single quotes change the way QV interprets the variables?  I've never used them before, but I'm fairly new.

[Anonymous]

At least it will interpret the date correctly.  For example, If I used a fixed date, it would be

where CRT_DT > '2014-05-30'

never

where CRT_DT > 2014-05-30

[Anonymous · Accepted solution]

Hi, Matthew.

The variable vQVDPurgeDate is not showing in the log because it is really not being set properly. Check the Variables Overview. Try changing the command to:

LET vQVDPurgeDate = chr(39) & date(today()-92,'YYYY-MM-DD') & chr(39);

Edit: You'll probably get an error on the select for the new transaction because vLastLoadDate is being set to YYYY-MM-DD.

Hope it helps.

Regards.

[#9]

Thank you Bruno!!  That was it I need to format it as date....Frustrating, but I hopefully won't make that oversight in the near future.  I really appreciate everyone's help!