Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Error: Garbage after statement

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;

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

8 Replies
maxgro
MVP
MVP

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


Not applicable
Author

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.

Not applicable
Author

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

Anonymous
Not applicable
Author

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

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


Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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.

Not applicable
Author

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!