Extract only the data from ksh script running netezza query

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hi
I searched this forum before posting the question, but couldnt find it, the issue
i'm facing is, i'm trying to select a column from a netezza table from a korn
shell script, but the query runs

var=$(nzodbcsql -q "select MAX(millcount) from table1";)
echo $var

it returns the value like below from the script

MAX --------------------- 45 Rows Returned : 1

is it possible to just extract only the "45" from the output, one thing to note
here is, the output can sometimes be a 1 digit or 2 digit or a 3 digit value

thanks
MJ

2

This is quite a basic problem, what have you actually tried so far?

3

i tried this command

awk -F '{ print $3}' <<<"${var}"

but no luck on it, then i thought of using the cut command, but due to the
varying number of digits each time, that would be a problem,

what i'm trying to find is more of a versatile solution, like few syntax's, where the query only returns the data and no header values,
but as i'm having few library issues in the netezza unix environment, i'm not able to use the "nzsql" option, i have to live with the nzodbcsql option

i'm still trying for other ways, meanwhile if you could provide a solution
that would be a great help

thanks
MJ

4

By using -F the way you did, you have no script ; only a very strange field separator. Simplify your script to just:

awk '{ print $3}' <<<"${var}"
5

Hi
thanks for your response, i have tried that, it seems the query header, the underline and the data all seems to be considered as the first value, below is the script and the output

var=$(nzodbcsql -q "select max(cnt) from tablename";)
echo $var
awk '{ print $1}' <<<"${var}"

Output

MAX ----- 12 Rows Returned : 1

MAX
-----
12

Rows

Is it possible to get only the value "12"

6

What happens if you change your script to what I suggested?:

var=$(nzodbcsql -q "select max(cnt) from tablename";)
awk '{ print $3}' <<<"${var}"
7 
var=$(nzodbcsql -q "select max(million_cnt) from IDM_DATALOAD_AUDIT where tablename = 'WI_PERSON'";)
echo $var
awk '{ print $3}' <<<"${var}"

output
------

MAX ----- 12 Rows Returned : 1





:

I'm getting only the ":" ( colon ) as the output

8

The echo without quotes must be hiding the true encoding of the contents of your output. Please change:

echo $var

to:

printf '%s\n' "$var" | od -c
9

this is the output i received when i change to printf '%s\n' "$var" | od -c

0000000  \n       M   A   X      \n   -   -   -   -   -  \n       1   2
0000020          \n  \n   R   o   w   s       R   e   t   u   r   n   e
0000040   d       :       1  \n
0000046

meanwhile when i worked on the script, using the below code i was able to get only the value, but is there any better way to do that

var=$(nzodbcsql -q "select max(million_cnt) from tablename";)
echo $var
awk '{ print $1}' <<<"${var}" | head -4 |tail -1
10

Or much more simply:

var=$(nzodbcsql -q "select max(million_cnt) from tablename";)
awk 'NR == 4{ print $1; exit }' <<<"${var}"
11

thanks a lot don
appreciated

Cheers
MJ