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
Steve wrote:
> 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?
Can you be more specific about the "Join error message"? Do you get an
Informix error number? What exactly does the error message tell you?
> 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
--
June Hunt
Yes, it says "erreur dans l'expression JOIN" since I have a french OS.
S.
"June C. Hunt" <june_c_hunt@hotmail.com> a 'crit dans le message de
news:c1gdhn$1j16qh$1@ID-209514.news.uni-berlin.de...
> Steve wrote:
> > 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?
>
> Can you be more specific about the "Join error message"? Do you get an
> Informix error number? What exactly does the error message tell you?
>
> > 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
>
> --
> June Hunt
>
>
Steve wrote:
> 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
In the light of other comments, you may be unable to do what you want
regardless, but... You cannot include an ORDER BY clause with an
INTO TEMP clause - or couldn't when I last tried to do it.
The other comments suggest that you can't do SELECT ... INTO TEMP with
the various MS-based tools. That suggests that they parse the first
word of the command (SELECT), spot that it is a SELECT, and assume it
will return data. SELECT ... INTO TEMP doesn't return data, of
course, so you don't declare a cursor for it in ESQL/C - you execute
it. To help you, the DESCRIBE statement return different statement
type code for SELECT INTO TEMP (SQ_SELINTO in sqlstype.h) and other
SELECT operations (0 - no official name in sqlstype.h).
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/