RE: Run SQL Statement with temp table from prompt
.. or even
UNLOAD TO someFile.unl
SELECT something FROM someTable
WHERE someTable.someDate = today
(having set DBDATE naturally)
Regards
Glyn Balmer
-----Original Message-----
From: Robert Stuart
[mailto:Robert.Stuart@nfer-nelson.co.uk]
Sent: 14 November 2000 14:04
To: Informix
Subject: 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 !)