read all columns from a text file in unix and frame sql query

0
votes

I have a column.txt file in unix folder having columns as below:

col1

col2

col3

i want to create a query like below by reading column.txt file using shell script:

SELECT SUM(CASE WHEN col1 is null then 1 else 0 end) as col1_null_count,

SUM(CASE WHEN col2 is null then 1 else 0 end) as col2_null_count,

SUM(CASE WHEN z is null then 1 else 0 end) as col3_null_count

FROM table;

1
sqlshellunix

1 Answers

0
votes

You need to use a for loop and iterate through each line of your column's file. Example below:

$ cat column.txt
col1
col2
col3
col4


echo "SELECT"
for i in `cat column.txt`
    do 
        echo "SUM(CASE WHEN $i is null then 1 else 0 end) as ${i}_null_count,"
    done
echo "FROM table;"