Re: Select ... INTO TEMP Table question

You can't ! And you can blame the MS-Query S##t ! It would be an awsome tool if you could do temporary tables... BTW: In Open Office you can't do it either ! Maybe if I could call a stored procedure in MS Query or in Open Office query that will do the trick... but I haven't tryed... Other possibility could be to use a special kind of select provided in latest version of the engine in which, if I'm not bad, you can do some kins of sub-select and treat the result as a table in the from clause of the main select... so you haven't to use temporary tables... (BTW: The resulting sintax could be not understood by MsQuery nor OpenOffice but you just use the native way to invoke querys of both tools). I saw something similar in previous posts to this forum... but you have to try for yourself... Hope this helps... Chucho! -----Original Message----- From: "Steve" <as@joe.ca> To: informix-list@iiug.org Date: Tue, 24 Feb 2004 14:46:41 -0500 Subject: Select ... INTO TEMP Table question Hi, I got a 3 tables query which drives crazy. I'm close of what I want but can't get it. I thought that I could create a temporary table then lauch a new query from there working with only 1 table. I us MS Query thru Excel against an Informix Database stored on Unix SCO server. When I add INTO TEMP tablename at the end of the query I got a Join error message so in this situation here how do I use Select ... INTO TEMP tablename? Here is the query: SELECT pritel.pri_in1_code, pritel.pri_date, pritel.pri_unit_vente, inv1.in1_desc_f, inv1.in1_cout_dern_ach, pritel.pri_prix, prix1.pr1_prix_vente1,pritel.pri_qte_main_tot FROM prog.pritel pritel, {oj prog.inv1 inv1 LEFT OUTER JOIN prog.prix1 prix1 ON prix1.pr1_in1_code=inv1.in1_code} WHERE inv1.in1_code=pritel.pri_in1_code AND prix1.pr1_in1_code = inv1.in1_code and prix1.pr1_unit_vente=pritel.pri_unit_vente AND pritel.pri_qte_main_tot <>0 AND pr1_date_deb = (select max (pr1_date_deb) from prix1 where prix1.pr1_in1_code = pritel.pri_in1_code AND prix1.pr1_unit_vente=pritel.pri_unit_vente ) ORDER BY pritel.pri_in1_code Thanks in advance Steve Amirault Jean Sagi jeansagi@myrealbox.com jeansagi@netscape.net sending to informix-list

Hi, And never forget: USER "WITH NO LOG" at end of sql statement, when you're creating temp table or putting the 'resultset' into temp table. Best regards. R. Ferronato "Jean Sagi" <jeansagi@myrealbox.com> wrote in message news:<c1gqvo$ctl$1@terabinaries.xmission.com>... > You can't ! > > And you can blame the MS-Query S##t ! > > It would be an awsome tool if you could do temporary tables... > > BTW: In Open Office you can't do it either ! > > Maybe if I could call a stored procedure in MS Query or in Open Office query that will do the trick... but I haven't tryed... > > Other possibility could be to use a special kind of select provided in latest version of the engine in which, if I'm not bad, you can do some kins of sub-select and treat the result as a table in the from clause of the main select... so you haven't to use temporary tables... (BTW: The resulting sintax could be not understood by MsQuery nor OpenOffice but you just use the native way to invoke querys of both tools). > > I saw something similar in previous posts to this forum... but you have to try for yourself... > > Hope this helps... > > Chucho! > > > -----Original Message----- > From: "Steve" <as@joe.ca> > To: informix-list@iiug.org > Date: Tue, 24 Feb 2004 14:46:41 -0500 > Subject: Select ... INTO TEMP Table question > > Hi, > I got a 3 tables query which drives crazy. I'm close of what I want but > can't get it. I thought that I could create a temporary table then lauch a > new query from there working with only 1 table. I us MS Query thru Excel > against an Informix Database stored on Unix SCO server. When I add INTO TEMP > tablename at the end of the query I got a Join error message so in this > situation here how do I use Select ... INTO TEMP tablename? > > Here is the query: > > SELECT pritel.pri_in1_code, > pritel.pri_date, > pritel.pri_unit_vente, > inv1.in1_desc_f, > inv1.in1_cout_dern_ach, > pritel.pri_prix, > prix1.pr1_prix_vente1,> pritel.pri_qte_main_tot > FROM prog.pritel pritel, {oj prog.inv1 inv1 LEFT OUTER JOIN prog.prix1 > prix1 ON prix1.pr1_in1_code=inv1.in1_code} > WHERE inv1.in1_code=pritel.pri_in1_code > AND prix1.pr1_in1_code = inv1.in1_code > and prix1.pr1_unit_vente=pritel.pri_unit_vente > AND pritel.pri_qte_main_tot <>0 > AND pr1_date_deb = (select max (pr1_date_deb) from prix1 > where prix1.pr1_in1_code = pritel.pri_in1_code > AND prix1.pr1_unit_vente=pritel.pri_unit_vente ) > ORDER BY pritel.pri_in1_code > > Thanks in advance > > Steve Amirault > > > > > Jean Sagi > jeansagi@myrealbox.com > jeansagi@netscape.net > > > sending to informix-list