RE: Informix to SQL 3 tables link problem

To bypass the MS Excel error, modify the sql directly as shown below: SELECT table1.new_prod_code, table2.description, table2.prod_cost, table3.unit_of_measure, table3.prod_price FROM table1, table2, OUTER(table3) WHERE table1.new_prod_code = table2.prod_code AND table2.prod_code = table3.prod_code Andy Bent -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Steve Sent: Tuesday, February 10, 2004 10:51 AM To: informix-list@iiug.org Subject: 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 sending to informix-list

Thanks Andy for the help but I tried this already and have a table OUTER not found or something. Steve "Bent_Andy" <abent@aetinc.com> a 'crit dans le message de news:c0b50r$8nh$1@terabinaries.xmission.com... > > > To bypass the MS Excel error, modify the sql directly as shown below: > > SELECT table1.new_prod_code, table2.description, table2.prod_cost, > table3.unit_of_measure, table3.prod_price > FROM table1, table2, OUTER(table3) > WHERE table1.new_prod_code = table2.prod_code > AND table2.prod_code = table3.prod_code> > Andy Bent > > > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > On Behalf Of Steve > Sent: Tuesday, February 10, 2004 10:51 AM > To: informix-list@iiug.org > Subject: 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 > > > > > > sending to informix-list