Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!!
maybe concat instead of concatenate?
Yes, sorry, I will edit my answer.
Thanks!!
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
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!
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.
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?
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!!