String to integer

Shell Programming and Scripting
1

I am on HP-UX using ksh in the script.

MaxSal=`sqlplus -silent /nolog <<EOF
connect / as sysdba
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off
select max(sal) from emp1; 
select max(sal) from emp2; 
select max(sal) from emp3;
exit;
EOF`


MaxSal1=`echo $MaxSal | awk '{print $1}'` 
MaxSal2=`echo $MaxSal | awk '{print $2}'` 
MaxSal3=`echo $MaxSal | awk '{print $3}'` 

I can echo MaxSal1 till this; but at next step it doesn't work
The below part is not workring.....ie MaxSal1,2,3 report null values, even though in the above i get positive numbers


EmpSal1=$(($MaxSal1 - 100))  
EmpSal2=$(($MaxSal2 - 100))  
EmpSal3=$(($MaxSal3 - 100))

Can someone help me

2

What is the error you are getting? Are you sure that "MaxSal1" and other variables are not empty? Do an echo for those variables and post the output.

3 
MaxSal=`sqlplus -silent /nolog <<EOF | tr -s '\n\' ' ' 
connect / as sysdba
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off
select max(sal) from emp1; 
select max(sal) from emp2; 
select max(sal) from emp3;
exit;
EOF`

The above output is all on one line

set -A arr $MaxSal
MaxSal1=${arr[0]}
MaxSal2=${arr[1]}
MaxSal3=${arr[2]}

This is horribly clunky, to say the least.
Consider using ksh arrays as a start.

set -A MaxSal=`sqlplus -silent /nolog <<EOF | tr -s '\n\' ' ' 
connect / as sysdba
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off
select max(sal) from emp1; 
select max(sal) from emp2; 
select max(sal) from emp3;
exit;
EOF`
4

Is the output from the SQL guaranteed to be a single line? If so, could you just read the output directly into variables a bit like this:-

sqlplus ........... <<EOF | read MaxSal1 MaxSal2 MaxSal3
connect / as sysdba
:
:
EOF

It seems to me that the output is actually three lines because you have three SELECT statements, but you could convert this to a single like with something more like this:-

SELECT e1.max(sal),e2.max(sal),e3.max(sal) from emp1 e1, emp2 e2, emp3 e3 ;

Does this help?

Robin