Re: Sum() function

Hmmm, I think you might want to investigate if you need a "HAVING", something like : SELECT inv1.in1_code, inv1.in1_desc_f,inv2.in2_qte_en_main FROM prog.inv1 inv1, prog.inv2 inv2 WHERE inv2.in2_in1_code = inv1.in1_code AND {fn LENGTH(inv1.in1_code)} <=6 AND inv1.in1_fo1_code_grp='1' HAVING SUM(inv2.in2_qte_en_main) = 0 ORDER BY inv1.in1_code I haven't run this, and don't even know if the syntax is correct. "Steve" <as@joe.ca> wrote in message news:<%C8Zb.13489$d34.1343784@news20.bellglobal.com>... > Hi, > > Here is a query I need to create. This query is run frm Excel (thru MS > Query) conencted with ODBC drivers to an Informix Database stored on Unix. > The quey posted here works rather well though I got a little problem in the > output result. Since"inv2.in2_qte_en_main " is a quantity field which has up > to 5 rows "inv2.in2" table, the output will show all records that contain 0. > BUT... There is rows that have another digit than 0 which means that the > real total is not 0 but all the sum of all rows of that table. So basically, > I need to sum "inv2.in2_qte_en_main " up dans compare it to 0. If it 0 than > is't OK but I can't get the SUM() function to work > please take a look at the query below to see how can I get this to work. > > Notice the {fn LENGTH... part which is required in Excel (MS Query) query to > run. I thought that it would be the case with SUM() but I have a "expression > mixes columns with aggregates" when I use it. I did the following: > {SUM(inv2.in2_qte_en_main)} =0 > > > > SELECT inv1.in1_code, inv1.in1_desc_f,> inv2.in2_qte_en_main > FROM prog.inv1 inv1, prog.inv2 inv2 WHERE inv2.in2_in1_code = inv1.in1_code > AND > {fn LENGTH(inv1.in1_code)} <=6 AND inv2.in2_qte_en_main =0 AND > inv1.in1_fo1_code_grp='1' ORDER BY inv1.in1_code > > > Thanks in advance. > > Steve Amirault