Help on 3 table informix query
Hi,
I just started a new post on a complex query that i have to built. I have to
make it work in an Excel sheet thru MS Query. It's a 3 table query which
make it difficult.
Here is the query:
SELECT pritel.pri_in1_code, pritel.pri_bar_code, pritel.pri_date_eff,
pritel.pri_unit_vente, pritel.pri_fact_conv, pritel.pri_prix,
pritel.pri_plx, pritel.pri_date,...
inv1.in1_retournable, inv1.in1_code_logiq
FROM {oj{oj prog.pritel pritel LEFT OUTER JOIN prog.inv1 inv1 ON
pritel.pri_code = inv1.in1_code} LEFT OUTER JOIN prog.prix1 prix1 ON
inv1.in1_code=prix1.pr1_in1_code} WHERE pritel.pri_code =
inv1.in1_code AND inv1.in1_code=prix1.pr1_in1_code
and I got the following in both MS Query and Vision SQL Gold:
[SCO Vision][ODBC Driver][Informix] A condition in the where clause results
in a two-sided outer join.
The Goal:
Table1 (prog.pritel pritel ) contains news price changes but to get a
detailled report I need both the cost and product descriptions which are in
Table2 (prog.inv1 inv1). Finally, I also need the sale price, U/M and few
more infos that are stored in Table3 (prog.prix1 prix1)
So I should be able to see all Table1 records whene running the fina
lversion of the query.
Help needed!!!!
Thanks in advance.
Steve Amirault
Hi,
Here is the working one:
SELECT pritel.pri_in1_code, pritel.pri_bar_code, pritel.pri_date,
pritel.pri_unit_vente,
prix1.pr1_fact_conv, pritel.pri_prix, pritel.pri_type, pritel.pri_p_pers,
pritel.pri_code, pritel.pri_qte_main_tot, inv1.in1_desc_f, inv1.in1_cout,prix1.pr1_prix_vente1
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 pr1_date_deb = (select max
(pr1_date_deb) from prix1 )
Thanks for your support guys, I really appreciated it.
Steve
"Steve" <as@joe.ca> a 'crit dans le message de
news:tFaWb.4650$lK.347321@news20.bellglobal.com...
> Hi,
>
> I just started a new post on a complex query that i have to built. I have
to
> make it work in an Excel sheet thru MS Query. It's a 3 table query which
> make it difficult.
>
> Here is the query:
>
> SELECT pritel.pri_in1_code, pritel.pri_bar_code, pritel.pri_date_eff,
> pritel.pri_unit_vente, pritel.pri_fact_conv, pritel.pri_prix,
> pritel.pri_plx, pritel.pri_date,> ...
> inv1.in1_retournable, inv1.in1_code_logiq
> FROM {oj{oj prog.pritel pritel LEFT OUTER JOIN prog.inv1 inv1 ON
> pritel.pri_code = inv1.in1_code} LEFT OUTER JOIN prog.prix1 prix1 ON
> inv1.in1_code=prix1.pr1_in1_code} WHERE pritel.pri_code =
> inv1.in1_code AND inv1.in1_code=prix1.pr1_in1_code
>
> and I got the following in both MS Query and Vision SQL Gold:
>
> [SCO Vision][ODBC Driver][Informix] A condition in the where clause
results
> in a two-sided outer join.
>
> The Goal:
>
> Table1 (prog.pritel pritel ) contains news price changes but to get a
> detailled report I need both the cost and product descriptions which are
in
> Table2 (prog.inv1 inv1). Finally, I also need the sale price, U/M and few
> more infos that are stored in Table3 (prog.prix1 prix1)
>
> So I should be able to see all Table1 records whene running the fina
> lversion of the query.
>
> Help needed!!!!
>
> Thanks in advance.
>
>
> Steve Amirault
>
>
>
>
>
>