Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I try to make an incremental load work by first loading the current data into FAC_OND_DELEN_X from qvd and then determine the latest record and store that into a variable Start_Key.
The I load the new records from the DB2 database except the variable Start_Key in the Where Clause doesn't work.
Who can help me?
FAC_OND_DELEN_X:
LOAD*
FROM $(PAD)FAC_OND_DELEN_INC.qvd (qvd);
LET Start_Key_Inc = Max(OND_KEY);
Concatenate
FAC_OND_DELEN_NEW:
LOAD *;
SQL SELECT *
FROM DWH.FAC_OND_DELEN
Where OND_ST In(1,6,7) And OND_NR_LEVERING <> 0 And OND_KEY > $(Start_Key_Inc); // Also Tried '$(Start_Key_Inc)'
I believe you need to use the Max() function within a LOAD statement.
Try like
FAC_OND_DELEN_X:
LOAD *
FROM $(PAD)FAC_OND_DELEN_INC.qvd (qvd);
MAX:
LOAD Max(OND_KEY) as Max
RESIDENT FAC_OND_DELEN_X;
LET Start_Key_Inc = Peek('Max', 0, 'MAX');
DROP TABLE MAX;
Concatenate
FAC_OND_DELEN_NEW:
LOAD *;
SQL SELECT *
FROM DWH.FAC_OND_DELEN
Where OND_ST In(1,6,7) And OND_NR_LEVERING <> 0 And OND_KEY > $(Start_Key_Inc); // Also Tried '$(Start_Key_Inc)'
I believe you need to use the Max() function within a LOAD statement.
Try like
FAC_OND_DELEN_X:
LOAD *
FROM $(PAD)FAC_OND_DELEN_INC.qvd (qvd);
MAX:
LOAD Max(OND_KEY) as Max
RESIDENT FAC_OND_DELEN_X;
LET Start_Key_Inc = Peek('Max', 0, 'MAX');
DROP TABLE MAX;
Concatenate
FAC_OND_DELEN_NEW:
LOAD *;
SQL SELECT *
FROM DWH.FAC_OND_DELEN
Where OND_ST In(1,6,7) And OND_NR_LEVERING <> 0 And OND_KEY > $(Start_Key_Inc); // Also Tried '$(Start_Key_Inc)'
Hi Mvdvosse
This works
LET vEscheme = 'PROD';
LET vVariable = 'CompanyA'
SQL SELECT *
FROM $(vEscheme).Table
WHERE FIELD1 = $(vVariable)
AND DATE >= '2013/01/01';
Hope this helps you
Joaquín
Hi Swuehl,
I don't realy see the difference. The variable has the right value. It is just that the database doesn't recognize the variable.
Thanx for suggesting though.
Michel
Hi:
Just try
LET Start_Key_Inc = chr(39) & Max(OND_KEY) & chr(39);
or
LET Start_Key_Inc = chr(39) & Peek('Max', 0, 'MAX') & chr(39);
Regards,
Joaquín
Michel,
the variable expansion should happen before the SQL code is sent to the DB.
When you run the code in debug mode of the script editor, what is shown as expanded code?
What do you expect to see?
Just a guess: maybe because OND_KEY contains non-numeric values? Meaning that you cannot compare them using a greater than operator?
There is also something wrong with your CONCATENATE LOAD syntax. The table name should either be eliminated (you're not creating a new table, you're just adding records to a table that already exists) or replaced by a table name to which you want to concatenate the new data, e.g.
CONCATENATE (FAC_OND_DELEN_X)
LOAD ...
Peter
Or if you create a log file during script execution, you will get an expanded SELECT statement as well. Does it match your expectations?
Peter,
I think we can assume a numeric variable value because of the max() function usage.
Well, I learned to never trust data quality on rumours alone... A simple confirmation would lay that one to rest. Let's call it "deduction by elimination".
That LET statement shouldn't work. Like you said, Max() must be used in a LOAD statement. That makes the situation a lot more awkward...
Peter