Informix to SQL 3 tables link problem
Hi,
I have an Excel sheet which has a query to an Informix database stored on a
Unix server. The query works fine but I need to query 3 tables and without
the parameter OUTER I don't have the proper result. I read somewhere that in
Excel you can't do a Outer join with 3 tables. Is there a way to get this
work?
Here is the situation:
Table1 contains new product codes to add to the system.
Table2 linked to Table1, is the inventory table which contains product code
descriptions, product costs and.
Table3 linked to Table2, contains product prices and measuring unit.
So I have to create a list of new product codes which shows descriptions and
prices. By doing this, I don't have all record of table1 because of Table3
missing codes. New product codes that don't have prices yet are ignored by
the query. So I found the parameter OUTER which should fix this but in Excel
it appears that a 3 table query won't allow the use of it.
Here is my query:
SELECT pritel.pri_in1_code, pritel.pri_bar_code, pritel.pri_date,
prix1.pr1_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.inv1 inv1, prog.pritel pritel, prog.prix1 prix1
WHERE pritel.pri_in1_code=inv1.in1_code AND prix1.pr1_in1_code =
inv1.in1_code AND pr1_date_deb = (select max (pr1_date_deb) from prix1
where prix1.pr1_in1_code=pritel.pri_in1_code)
What would be solutions to this problem.
Any help will be greatly appreciated
Steve wrote: > Hi, > > I have an Excel sheet which has a query to an Informix database stored on a > Unix server. The query works fine but I need to query 3 tables and without > the parameter OUTER I don't have the proper result. I read somewhere that in > Excel you can't do a Outer join with 3 tables. Is there a way to get this > work? > > Here is the situation: [snipped] I cannot comment on the Excel limitation that you describe, but as to a possible solution... how about creating a view (using OUTER as needed) and use the view rather than your tables when populating your spreadsheet? -- June Hunt
Here is something I worked on and run well but need to add a third table
which mean a second OUTER join.
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_type, pritel.pri_date_fin, pritel.pri_p_pers, pritel.pri_code,
pritel.pri_plx, pritel.pri_date, inv1.in1_fo1_code, inv1.in1_code,
inv1.in1_code_fou, inv1.in1_cat, inv1.in1_inter, inv1.in1_mineur,
inv1.in1_desc_f, inv1.in1_desc_a, inv1.in1_cout, inv1.in1_date_dern_vent,
inv1.in1_date_dern_ach, inv1.in1_in7_type, inv1.in1_indic2, inv1.in1_poids,
inv1.in1_fact_conv, inv1.in1_unit_ach, inv1.in1_marge, inv1.in1_me1_code,
inv1.in1_mult_prix, inv1.in1_mult_cout, inv1.in1_nbr_jour_liv,inv1.in1_fo1_code_grp, inv1.in1_retournable, inv1.in1_code_logiq
FROM {oj prog.pritel pritel LEFT OUTER JOIN prog.inv1 inv1 ON
pritel.pri_bar_code = inv1.in1_bar_code}
WHERE pritel.pri_code = inv1.in1_code
Can you help me?
Thanks
Steve
"Steve" <as@joe.ca> a 'crit dans le message de
news:l47Wb.4588$lK.324404@news20.bellglobal.com...
> Hi,
>
> I have an Excel sheet which has a query to an Informix database stored on
a
> Unix server. The query works fine but I need to query 3 tables and without
> the parameter OUTER I don't have the proper result. I read somewhere that
in
> Excel you can't do a Outer join with 3 tables. Is there a way to get this
> work?
>
> Here is the situation:
> Table1 contains new product codes to add to the system.
> Table2 linked to Table1, is the inventory table which contains product
code
> descriptions, product costs and.
> Table3 linked to Table2, contains product prices and measuring unit.
>
> So I have to create a list of new product codes which shows descriptions
and
> prices. By doing this, I don't have all record of table1 because of
Table3
> missing codes. New product codes that don't have prices yet are ignored by
> the query. So I found the parameter OUTER which should fix this but in
Excel
> it appears that a 3 table query won't allow the use of it.
>
> Here is my query:
>
> SELECT pritel.pri_in1_code, pritel.pri_bar_code, pritel.pri_date,
> prix1.pr1_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.inv1 inv1, prog.pritel pritel, prog.prix1 prix1
> WHERE pritel.pri_in1_code=inv1.in1_code AND prix1.pr1_in1_code =
> inv1.in1_code AND pr1_date_deb = (select max (pr1_date_deb) from prix1
> where prix1.pr1_in1_code=pritel.pri_in1_code)
>
>
> What would be solutions to this problem.
>
> Any help will be greatly appreciated
>
>
Hi,
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_type, pritel.pri_date_fin, pritel.pri_p_pers, pritel.pri_code,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_bar_code = inv1.in1_bar_code} LEFT OUTER JOIN prog.prix1 prix1 ON
inv1.in1_unit_vente=prix1.pr1_unit_vente} WHERE pritel.pri_code =
inv1.in1_code AND inv1.in1_code=prix1.pr1_in1_code
And I got this:
[SCO Vision][ODBC Driver][Informix] A condition in the where clause results
in a two-sided outer join.
I'm close I think though I'm still missing somethig.
Steve
"Steve" <as@joe.ca> a 'crit dans le message de
news:l47Wb.4588$lK.324404@news20.bellglobal.com...
> Hi,
>
> I have an Excel sheet which has a query to an Informix database stored on
a
> Unix server. The query works fine but I need to query 3 tables and without
> the parameter OUTER I don't have the proper result. I read somewhere that
in
> Excel you can't do a Outer join with 3 tables. Is there a way to get this
> work?
>
> Here is the situation:
> Table1 contains new product codes to add to the system.
> Table2 linked to Table1, is the inventory table which contains product
code
> descriptions, product costs and.
> Table3 linked to Table2, contains product prices and measuring unit.
>
> So I have to create a list of new product codes which shows descriptions
and
> prices. By doing this, I don't have all record of table1 because of
Table3
> missing codes. New product codes that don't have prices yet are ignored by
> the query. So I found the parameter OUTER which should fix this but in
Excel
> it appears that a 3 table query won't allow the use of it.
>
> Here is my query:
>
> SELECT pritel.pri_in1_code, pritel.pri_bar_code, pritel.pri_date,
> prix1.pr1_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.inv1 inv1, prog.pritel pritel, prog.prix1 prix1
> WHERE pritel.pri_in1_code=inv1.in1_code AND prix1.pr1_in1_code =
> inv1.in1_code AND pr1_date_deb = (select max (pr1_date_deb) from prix1
> where prix1.pr1_in1_code=pritel.pri_in1_code)
>
>
> What would be solutions to this problem.
>
> Any help will be greatly appreciated
>
>