How to pass a series of input values to sql query?

[#1]

I need to send a bunch of VIN numbers as an input to an sql query.The VIN numbers can number anywhere between 100-1000 and can vary to the user's requirement. I need all the VIN numbers to be input at once and not one by one. Is there a way to do it?

[Anonymous]

Hi Madhusudhan,

Can they be entered as a txt or csv file?

If yes, load this file and then concatenate the values:

NumInputValues:
LOAD
     COUNT(InputValue) as NumInputValues
FROM YourFile;

LET vNumInputValues = Num(Peek('NumInputValues',0,'NumInputValues'));

IF $(vNumInputValues) > 0 THEN

     InputValues:
     LOAD
          //Concatenate(DISTINCT InputValue,',') as InputValues -- EDITED
          Concat(DISTINCT InputValue,',') as InputValues
     FROM YourFile;

     LET vInputValues = 'WHERE VIN IN (' & Peek('InputValues',0,'InputValues')&')';
ELSE

     LET vInputValues = '';

END IF;
SQLSteatment:
SQL SELECT * FROM YourSqlTable
$(vInputValues);

Regards!!

[maxgro]

maybe concat instead of concatenate?

[Anonymous]

Yes, sorry, I will edit my answer.

Thanks!!

[jonathandienst]

You don't need to load a count beforehand. This script should suffice:

InputValues: 

LOAD 

  Concat(DISTINCT InputValue,',') as InputValues 

FROM YourFile; 

If Alt(NoOfRows(InputValues), 0) > 0 Then

   LET vInputValues = 'WHERE VIN IN (' & Peek('InputValues',0,'InputValues')&')'; 

ELSE 

   SET vInputValues = ''; 

END IF

Result:

SQLSteatment: 

SQL SELECT * FROM YourSqlTable 

$(vInputValues

[Anonymous]

Hi Jonathan,

I like to do a count before because you can handle distinct values, with num of rows you can't handle distinct values.

Regards!

[#7]

They could be entered as a text file but is there a way I can just paste the VIN numbers using the input box?

I could use the let command in my script editor to point to that variable which holds the VIN numbers.

[#8]

The whole objective of the sql query is to pull a bunch of numbers for particular VIN's.Could I use the input box to do the said operation? I could upload a txt or csv file  but I am just wondering if the input box could be used for this operation?

[Anonymous]

Hi,

You can insert them on a variable, separated by comma, like 1000,1300,1200,...

Then call your variable on your script like this:

IF Len('$(vNumInputValues)') > 0 THEN 

     YourSQL:
    SQL SELECT * FROM YourTable
    WHERE VIN IN ('$(vNumInputValues)');
ELSE 
     YourSQL:
    SQL SELECT * FROM YourTable

END IF;

Create one action on your variable to reload when input has changed or create a button to reload app.

Regards!!