How to use In clause with big list of commas separated values

[ivishalgandhi]

how to use env variable/variable with big list of comma sep values

e.g.
shell : export USER_NAME= "'u1','u2 2','u3'"
csvq 'select * from users where user_name in $USER_NAME'

[derekmahar]

As far as I know, csvq cannot replace references to environment variables in its scripts. However, you can substitute such references using the shell command envsubst:

$ export USERS="'u1','u2','u3'"
$ echo 'SELECT * FROM STDIN WHERE user in ($USERS);' | envsubst
SELECT * FROM STDIN WHERE user in ('u1','u2','u3');

[ondohotola]

And yet another command I didn't know :-)-O

Even through the answer lies on the quotes and I would think that

csvq "select * from users where user_name in $USER_NAME" 

should work.

[derekmahar]

Good point. You would typically use envsubst when the SQL query and environment variable references are inside a text file separate from the shell script that invokes csvq. You could think of this text file as a template that contains environment variables that envsubst replaces in order to create the final script. I used an echo command and string just to illustrate in one line how envsubst works. A more realistic example would have first written this string to a text file and then redirected that file into envsubst:

$ export USERS="'u1','u2','u3'"
$ echo 'SELECT * FROM STDIN WHERE user in ($USERS);' > select.sql.template 
$ envsubst < select.sql.template
SELECT * FROM STDIN WHERE user in ('u1','u2','u3');

[mithrandie]

You can use the execute command to execute a string as a query.

$ csvq "EXECUTE 'SELECT * FROM users WHERE user_name IN (%s)' USING @%USER_NAME"

@%USER_NAME points to the environment variable $USER_NAME.