Null return from a vertica query

[pk132007]

I am trying to run a vertica sql query
Select SUM(column1) from table;

If column1 has null value for all the records, then the query will return a single character.
I have to capture it in a varaible in unix so that i can compare it.

var=$(/opt/vertica/bin/vsql -c "Select SUM(column1) from table;")

If [[ $var = " " ]] ; then
echo "print"
fi

i am not able to get the "print" output.
Can anyone please help.

PS: In db2 the output of above query is "-" so we can easily compare.

[nnani]

#pk132007

I could not get your requirement. I am projection a solution according to my understanding

Scenario : If column1 has null value for all the records, then the query will return a single character.

Code: Select all

nnani=> \d+ sum_test
                                         List of Fields by Tables
       Schema       |  Table   |  Column   | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------------------+----------+-----------+------+------+---------+----------+-------------+-------------
public| sum_test | digit_col | int  |    8 |         | f        | f           |
(1 row)



nnani=> select sum(digit_col) from sum_test;
 sum
-----
  11
(1 row)

Script getting the sum

Code: Select all

#!/bin/bash

add_var=`vsql -At -c " select sum(digit_col) from sum_test;"`

if [ -z  "$add_var" ];
then
add_var="-"
echo "$add_var"
echo "print"
else
echo "$add_var"
echo "records found"
fi

Let me if, this is what you were looking for.
Hope this helps..

[JimKnicely]

Perhaps this is another option?

Code: Select all

#!/bin/bash

var=$(/opt/vertica/bin/vsql -At -c "SELECT NVL(SUM(column1)::VARCHAR, '-') FROM table;")

if [ "$var" == "-" ]; then
  echo "print";
fi

[JimKnicely]

Yet another example:

Code: Select all

daniel@synapse:~$ var=`/opt/vertica/bin/vsql -Atqnc "select sum(null::int)"`
daniel@synapse:~$ if [[ -z ${var} ]]; then echo 'EMPTY'; else echo ${var}; fi
EMPTY

[nnani]

Another Possibility

Code: Select all

nnani=> select sum(digit_col) from sum_test;
 sum
-----
  11
(1 row)

nnani=> select CASE when sum(digit_col) < 1 then '-' ELSE 'EMPTY' END from sum_test;
 case
-------
 EMPTY
(1 row)

You can use this in script like this

Code: Select all

#!/bin/bash

add_var=`vsql -At -c " select CASE when sum(digit_col) < 1 then '-' ELSE 'EMPTY' END from sum_test;"`
echo "$add_var"

Hope this helps..