4 Replies Latest reply: May 14, 2015 5:23 AM by Anupam Singh RSS

    Using variables in Load statement

      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

        • Re: Using variables in Load statement
          Miguel Angel Baeyens de Arce

          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

            • Re: Using variables in Load statement

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

                • Re: Using variables in Load statement
                  Miguel Angel Baeyens de Arce

                  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

              • Re: Using variables in Load statement

                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.

                gr1.png

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

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