Script Variable Usage in SQL Where Clause

[#1]

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)'

[swuehl · Accepted solution]

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)'

[JoaquinLazaro]

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

[#4]

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

[JoaquinLazaro]

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

[swuehl]

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?

[Peter_Cammaert]

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

[Peter_Cammaert]

Or if you create a log file during script execution, you will get an expanded SELECT statement as well. Does it match your expectations?

[swuehl]

Peter,

I think we can  assume a numeric variable value because of the max() function usage.

[Peter_Cammaert]

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