Run SQL Statement with temp table from prompt
Hi Everybody
I'm trying to run a SQL statement from the command prompt
that includes 2 select statements.
The 1 st statement creates a temp table and I'm trying to
retrieve data from the created temp table but there
are no rows retrieved.
I think because of the ; the temp table is removed.
I removed the ; but then I got an syntax error.
INFORMIXSERVER=serverexport
INFORMIXSERVER
INFORMIXDIR=/opt/informixexport
INFORMIXDIR
$INFORMIXDIR/bin/dbaccess dcs <<
!!
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
!!
I would appreciate your comments...
-------------------------------
Ilhan Erden
e-mail : ilerden@ea-dc.dhl.com
-------------------------------
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 !)
"Ilhan Erden" <ilerden@ea-dc.dhl.com> wrote in message
news:8up9av$mt4$1@news.xmission.com...
>
> Hi Everybody
>
> I'm trying to run a SQL statement from the command prompt
> that includes 2 select statements.
> The 1 st statement creates a temp table and I'm trying to
> retrieve data from the created temp table but there
> are no rows retrieved.
> I think because of the ; the temp table is removed.
> I removed the ; but then I got an syntax error.
>
>
> INFORMIXSERVER=server> export
> INFORMIXSERVER
> INFORMIXDIR=/opt/informix> export
> INFORMIXDIR
> $INFORMIXDIR/bin/dbaccess dcs <<
> !!
> 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
> !!
>
> I would appreciate your comments...
What about encapsulation the whole into
begin work;
<insert sql stuff here >
commit work;
HTH
Christian
--
The opinions stated above are my own
and not necessarily those of my employer.
>
> -------------------------------
> Ilhan Erden
> e-mail : ilerden@ea-dc.dhl.com
> -------------------------------
Try something like this
dbaccess databasename <<! sql statements seperated by ; !>>
hth
savio
Ilhan Erden wrote:
> Hi Everybody
>
> I'm trying to run a SQL statement from the command prompt
> that includes 2 select statements.
> The 1 st statement creates a temp table and I'm trying to
> retrieve data from the created temp table but there
> are no rows retrieved.
> I think because of the ; the temp table is removed.
> I removed the ; but then I got an syntax error.
>
> INFORMIXSERVER=server> export
> INFORMIXSERVER
> INFORMIXDIR=/opt/informix> export
> INFORMIXDIR
> $INFORMIXDIR/bin/dbaccess dcs <<
> !!
> 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
> !!
>
> I would appreciate your comments...
>
> -------------------------------
> Ilhan Erden
> e-mail : ilerden@ea-dc.dhl.com
> -------------------------------
Ilhan Erden wrote:
> ...
> $INFORMIXDIR/bin/dbaccess dcs <<
> !!
> 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
> !!
>
You could do this in a single SQL statement, although the temp table
variation should work as well. Whatever you do, run it in interactive
dbaccess first and ensure that its working.
unload to gunluk_rapor
select gun,ay,yil,shipperaccount,airwaybill,consignee_code,cons_iata,
cons_zipcode,cons_countrycode,exception_msg
from tarih_ayrilmis
where "20" || shipmentdate[1,2] =year(today-1)
and shipmentdate[3,4] =month(today-)
and shipmentdate[5,6] =day(today-1);
Also, note the use of day/month/year(today-1) as against day(today)-1.
The former avoids the problem that would occur on the 1st of the
month/year
Rudy
I think many people may be missing the point of your TEMP table (or I'm
reading it wrong) which is to convert your 2-digit Year to a 4-digit Year,
thus matching the "current" output. Try this:
(I'm assuming you don't want to change your output format)
unload to gunluk_raporselect "20"||shipmentdate[1,2], shipmentdate[3,4], shipmentdate[5,6], *
from esd_exception
where "20"||shipmentdate[1,2]=year(current)
and shipmentdate[3,4]=month(current)
and shipmentdate[5,6]=day(current)-1
Just remember to run the command after midnight int he Cron.
Mike Hoffman
In <8up9av$mt4$1@news.xmission.com> article, Ilhan Erden mentioned that:
: Hi Everybody
: I'm trying to run a SQL statement from the command prompt
: that includes 2 select statements.
: The 1 st statement creates a temp table and I'm trying to
: retrieve data from the created temp table but there
: are no rows retrieved.
: I think because of the ; the temp table is removed.
: I removed the ; but then I got an syntax error.
: INFORMIXSERVER=server
: export
: INFORMIXSERVER
: INFORMIXDIR=/opt/informix
: export
: INFORMIXDIR
: $INFORMIXDIR/bin/dbaccess dcs <<
: !!
: 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
: !!
: I would appreciate your comments...
: -------------------------------
: Ilhan Erden
: e-mail : ilerden@ea-dc.dhl.com
: -------------------------------