To read the values and to use in the where condition

Shell Programming and Scripting
1

Hi,

I have the below values in a text file.

'xx.16397950',
'xx.16397957',
'xx.16397976',
'xx.16473259',

I need to use in the where clause of the sql query in my unix shell script. How to read one by one value.

It should be like this:

select * from <<table name>> where xx in ( the above values )

Please guide.

Many thanks in advance.

2

Hi,

try this,

xx_value=`paste -s inputfile| sed 's/,$//g'`
echo "select * from <<table name>> where xx in ( $xx_value )"
3

Giving the below error:

xx_value: command not found

I'm using Linux and executing the script like below:

./<<name of the script>> <<argument>>
4

Post your code. What argument are you passing to script.

5

Below is the code

#!/bin/bash
read shipments
sort shipments | uniq > test1.txt
sed "s/.*/'TT.&',/;$ s/,//" test1.txt > output
value = `paste -s output`
echo "select * from tn where xx in ($value)"

Executed as below:

./xx shipments
6

Hi

The error is because you have a space before and after =. Remove the spaces.

value=`paste -s output`

Guru.

7

One more suggestion , Instead of creating output file, you can pipe the output to paste command as follow.

#!/bin/bash
read shipments
sort shipments | uniq > test1.txt
value=`sed "s/.*/'TT.&',/;$ s/,//" test1.txt | paste -s`
echo "select * from tn where xx in ($value)"
8

Thanks a lot pravin. However i'm getting spaces in between the values. How to squeeze the extra space in between the values.

Values should come like this in the where clause of the sql query:

'TT.16397950','TT.16397957','TT.16397976','TT.16473259'
9

Try paste command with "-d" option as follow.

value=`sed "s/.*/'TT.&',/;$ s/,//" test1.txt | paste -s -d ''
10

Thanks a lot pravin. It is giving as expected.

one more query:

How the variable "output" can be used in the sql query after getting connected to database.

#!/bin/bash
sort shipments | uniq > test1.txt
sed "s/.*/'TESCO.&',/;$ s/,//" test1.txt | paste -s -d '' > output
sqlplus glo/glo@tcot
select * from xx where column_name in ($output)

In this case, am getting connected to database, but cursor is waiting at sql prompt, with out executing the above select query.

Please help.

11

Why are you taking output in the file ?

output=`sed "s/.*/'TESCO.&',/;$ s/,//" test1.txt | paste -s -d ''`
sqlplus glo/glo@tcot <<ENDOFSQL
select * from xx where column_name in ($output);
ENDOFSQL