Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to write an incremental load statement however my where clause is failing me. Could you kindly guide me on what I might be doing wrong:
Data_2014Q3update:
LOAD * FROM
(qvd);
SQL Select *
FROM 'Data_2014Q3update'
WHERE [Vendor Name] <> 'ABC, INC'
AND Year <> 2013;
Thank you!
what error message are you receiving?
for incremental load review the help file:
QVD files and Incremental Load
Hi,
You can read this good incremental load docs
Regards
Anand
Hector,
My load completes without the Where clause conditions being met.
Anand,
I have read these documents multiple times, however they did not help.
I might have issues with my syntax:
1) Within the Where clause my field name has a "space". Am I using the right syntax in this case?
2) I am trying to load all data except the conditions identified in where clause. I am looking to confirm the syntax in this case.
Hector,
Here is the screenshot of the Script Error message:
Here an example, based on a table which has a date as key field. In short terms, first it checks if the file exists, then reads all the records starting from the last date saved and as last step, stores everything into the history table, that contains the whole data.
HOPE it helps you
//CHECK FILE EXISTS (0= EXISTS , -1= NOT EXISTS)
LET var_qvdexists=isnull(QvdCreateTime('$(VAR_YOURPATH)$(VAR_YOURTABLE)'));
//IF THE FILE EXISTS, GET THE LAST DATE STORED
//ELSE START FROM TODAY - NUMBER OF MONTH (as parameter)
IF ($(var_qvdexists) = 0) THEN
YOURTABLE:
LOAD max(date(floor(DATEFIELD))) as MAXDATE
FROM $(VAR_YOURPATH)$(VAR_YOURTABLE) (qvd)
;
let var_startdate = peek('MAXDATE');
let var_startdate = date(num(var_startdate), 'YYYY.MM.DD');
Drop table YOURTABLE;
ELSE
let var_startdate = date(AddMonths(today(), -$(var_backmonths), 0), 'YYYY/MM/DD');
END IF
//INCREMENTAL LOADING
BUFFER:
LOAD
YOUR FIELDS,
DATEFIELD
;
SQL SELECT *
FROM YOURTABLE
WHERE convert(nvarchar,DATEFIELD,102) >= '$(var_startdate)'
// AND convert(nvarchar, DATEFIELD, 102) < '$(var_udate)'
;
//HISTORY FILE
History:
load *, '1' as flag resident BUFFER;
IF ($(var_qvdexists) = 0) THEN
Concatenate
load *, '1' as flag
from $(VAR_YOURPATH)$(VAR_YOURTABLE) (qvd)
WHERE NOT EXISTS (DATEFIELD, DATEFIELD)
;
ENDIF
drop field flag;
drop table BUFFER;
STORE History INTO $(VAR_YOURPATH)$(VAR_YOURTABLE);
DROP TABLE History;
reading your first post, if I understood, the sql should be:
FROM 'Data_2014Q3update'
WHERE [Vendor Name] NOT IN 'ABC, INC'
AND Year <> 2013;
That returned the same error...
start with the sql (and also let we know which database are you using as the sql syntax depends on db)
SQL Select *
FROM 'Data_2014Q3update';
does it works?
yes, add another line
.......
WHERE [Vendor Name] <> 'ABC, INC';
does it works?
I don't think, maybe
WHERE [Vendor Name] not in ('ABC', 'INC')