What I am trying to do is check if the database query returned any records.
If no records returned then output a message else output results to a file.
Right now if I take out the if and else statements the code runs fine and sends the email. If no records returned the email sends the column names. I would like to send a message instead of the column heading.
Here is my code can anyone help?

#!/bin/ksh
#cd /home/john/

. sybase
. ss_pto.id

echo "****** Please do not reply to this message ******" > ss_pto.status
echo " " >> ss_pto.status
echo New PTO and Special Requests ADDED as of: `date` >> ss_pto.status
echo " " >> ss_pto.status
echo Please add to Outlook calendars: >> ss_pto.status
#export today=`date`


date '+%m %d %Y %H %M %S' |
{
read MONTH DAY YEAR HOUR MINUTE SECOND
DAY=`expr "$DAY" - 1`
case "$DAY" in
0)
MONTH=`expr "$MONTH" - 1`
case "$MONTH" in
0)
MONTH=12
YEAR=`expr "$YEAR" - 1`
;;
esac
DAY=`cal $MONTH $YEAR | grep . | fmt -1 | tail -1`
esac

}
tempdate="$YEAR"-"$MONTH"-"$DAY"" ""$HOUR":"$MINUTE":"$SECOND"


isql -U $U -P $P -S $S -w1000 -o tmp_isql.status << label1
use ptodb_rch01_prod
go
select RTRIM(usr.fname) "First name", convert(char(20), usr.lname) "Last name", convert(char(15), ind.pto_date, 107) "PTO Date", ind.comments "Comments"
from ss_pto_user usr, ss_pto_index ind
where
ind.user_id = usr.user_id and ind.request_date >= '$tempdate' and (ind.class_id = 7 and ind.section_id = 5 or ind.class_id = 2 and ind.section_id = 4 )
order by usr.lname asc

go
label1


reccnt=`wc -l tmp_isql.status`
if [reccnt = 0]
message="no records returned"
echo $message>>ss_pto.status
else
cat tmp_isql.status>>ss_pto.status

mailx -s"SS PTO" -senders email reciepents email < ss_pto.status

The reason is sql returns like this if there are no matching records.

column1 column2 column3 ...
------------------------------------------------------
(0 rows affected)

The reccnt=`wc -l tmp_isql.status` always returns more than 0.

May be you can try this.
recount='cat tmp_isql.status |awk 'NR >3' |grep -v - | grep -v return'

if [$recount != "(0 rows affected)"]
message="no records returned"
echo $message>>ss_pto.status
else
cat tmp_isql.status>>ss_pto.status

mailx -s"SS PTO" -senders email reciepents email < ss_pto.status

Here change ' NR>3' according to the number of lines for columns headings

Hope this piece of code is useful to you.

Thanks I got it to work just had one trouble and that was the variable $recount ended up having a carriage return in it so the compare kept failing. I ended up writing it to a file then using the file results. I am not sure if there is a better or easier way but here is what I did.

recount='cat tmp_isql.status |awk 'NR >1' |grep -v - | grep -v return'

echo $recount>temprecount
recount2=`cat temprecount`

if ["$recount2" = "(0 rows affected)"]
message="no records returned"
echo $message>>ss_pto.status
else
cat tmp_isql.status>>ss_pto.status

Jean-Pierre.

isql has the -b option which strips off the header...

isql -U $U -P $P -S $S -w1000 -b -o tmp_isql.status