Using variables in Load statement

[#1]

Hi , I'm trying to limit the data that gets loaded into a view. I have created a couple of variables that represent outstanding quantity and un-invoiced quantity. However , when I try and included them in the load ( if either are greater than zero ) they are not recognised. When I look at the variables available in the script they do not appear. Is it not possible to do this or have I not defined them correctly ? Many thanks. Simon

[Miguel_Angel_Baeyens]

Hi Simon,

It's difficult to give the right answer without checking the code, but anyway, yours should look like this

// Variable definition and assignment
SET vHighLimit = 3000;
SET vLowLimit = 50;
// WHERE clause in the LOAD statement using both variables
Invoices:
LOAD InvoiceID,
     CustomerID,
     Date,
     Amount
WHERE Amount >= $(vLowLimit) AND Amount <= $(vHighLimit);
SQL SELECT InvoiceID,
     CustomerID,
     Date,
     Amount
FROM db.Table;

Hope that helps.

Miguel

[#3]

Just to make things right, there is one small mistake:

Instead of

WHERE Amount >= $(vLowLimit) AND Amount <= $(vHighLimit);

you shoud write

WHERE Amount >= '$(vLowLimit)' AND Amount <= '$(vHighLimit)';

[Miguel_Angel_Baeyens]

Hi,

Thanks for noting, but that will only apply if the WHERE Amount is expecting a literal or a string, if it's expecting a numeric value, then you must not use the quotes. And since the Amount field usually stores numeric values, therefore my use of the variables without quotes.

Actually, depending on the driver and the database, using the quotes is casting the value in the variable to string, and it might make the load slower, as the driver has to prior cast the value to numeric, then compare to match the WHERE clause.

Regards,

Miguel

[#5]

before using the variable in the load statement of script, set a field level trigger on the particular field whose value will be stored in variable.

Add on select action as set variable.

Give the variable name. and in expression mention the field name.

using it in the load statement will fetch the desired results.