RE: Run SQL Statement with temp table from prompt
try something like
#!bin/sh
ECHO=/bin/echo ; export ECHO
TMP=tmp.sql ; export TMP
DATE=`date +%y%m%d` ; export DATE
DBACCESS=${INFORMIXBIN}/dbaccess ; export DBACCESS
${ECHO} UNLOAD TO someFile.unl
${ECHO} SELECT something > ${TMP}
${ECHO} FROM someTable >> ${TMP}
${ECHO} WHERE someTable.someDate = \\"${DATE}\\" >> ${TMP}
${DBACCESS} database ${TMP}
# and mail file here
#/bin/rm ${TMP}
-----Original Message-----
From: Ilhan Erden [mailto:ilerden@ea-dc.dhl.com]
Sent: 14 November 2000 13:31
To: Bogdan Neagu; Informix
Subject: Re: Run SQL Statement with temp table from prompt
Hi Bogdan
Actually what I wanted to do is to unload to a file on a daily basis
and send this file via mail to relevant staff.
I m trying to do this automatically by adding this script into
the cron.
The script should only retrieve the records of the current date.
Unfortunately the data type of shipmentdate is char(8) for example
"001123" which means 23 rd November 2000.
Therefore I'm creating a temp table with 3 fields which are
yil representing year, ay representing month and gun representing
day.
The 2 nd select statement compares each of them with the current
date values.
I have this strange feeling that there is a very simple way to do
this but I don't know how ???
Best Regards
Ilhan
Bogdan Neagu wrote:
>
> Ilhan Erden wrote:
>
> > select *, "20"|| shipmentdate[1,2] yil,shipmentdate[3,4]
> > ay,
> > shipmentdate[5,6]> > gun
> > from
> > esd_exception
> > into temp tarih_ayrilmis with no
> > log;
> > unload to
> > gunluk_rapor
> > select
> > gun,ay,yil,shipperaccount,airwaybill,consignee_code,cons_iata,
> > cons_zipcode,cons_countrycode,exception_msg from
> > tarih_ayrilmis
> > where yil=year(current) and ay=month(current) and
> > gun=day(current)-1
>
> Do you want to find out the date of the gun shipment ?
>
> (sorry, couldn't miss that one !)