SQL question
Hi,
I am newbie about SQL and I would like to have 2 sql statements joined on same
query. The second one only execute if table exists on database, I am using
shell code for it but it takes some time to get results...
My code:
select x0.sid,
x0.username,
x0.hostname,
x1.sqs_dbname,
dbinfo("UTC_TO_DATETIME",x0.connected) AS conn_dt
from
sysmaster:"informix".syssessions x0,
sysmaster:"informix".syssqlstat x1,sysmaster:"informix".sysnetworkio n
where ( x0.sid = x1.sqs_sessionid ) and hostname is not NULL and
trim(hostname) <> ''
and trim(hostname) <> '-' AND sqs_dbname NOT LIKE 'sys%' AND sqs_dbname <> '-'
AND n.sid = x0.sid;
Second query:
select first 1 sid_f_lig, max(dtlogin_f_lig), winuser_f_lig, tty_f_lig,cod_capli from grlf_lig where
( sid_f_lig = '$SID' and dtlogout_f_lig is null )
group by sid_f_lig, winuser_f_lig, tty_f_lig, cod_capli
Can someone give some help how to do it?
Thanks in advance,
SP
All code:
dbaccess sysmaster <<EOF > /dev/null 2> /dev/null
set isolation to dirty read;
unload to '$TMPFILE' delimiter "|"
select x0.sid,
x0.username,
x0.hostname,
x1.sqs_dbname,
dbinfo("UTC_TO_DATETIME",x0.connected) AS conn_dt
from
sysmaster:"informix".syssessions x0,
sysmaster:"informix".syssqlstat x1,sysmaster:"informix".sysnetworkio n
where ( x0.sid = x1.sqs_sessionid ) and hostname is not NULL and
trim(hostname) <> ''
and trim(hostname) <> '-' AND sqs_dbname NOT LIKE 'sys%' AND sqs_dbname <>
'-' AND n.sid = x0.sid
EOF
for I in `cat $TMPFILE`; do
SID=`echo $I | awk -F'|' '{print $1}'`
BD=`echo $I | awk -F'|' '{print $4}'`
echo "" > $FILE
dbaccess $BD <<EOF > /dev/null 2> /dev/null
set isolation to dirty read;
unload to "$FILE" delimiter '|'
select first 1 sid_f_lig, max(dtlogin_f_lig), winuser_f_lig, tty_f_lig,cod_capli from grlf_lig where
( sid_f_lig = '$SID' and dtlogout_f_lig is null )
group by sid_f_lig, winuser_f_lig, tty_f_lig, cod_capli
EOF
if [ -s $FILE ]; then
cat $FILE >> $FILE1
fi
done