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 : -------------------------------