Re: Sum() function

Steve wrote: > 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 Why? See my comment below about the {comment} notation in Informix SQL. [This note was added at the last moment - when reviewing the question before sending the answer.] > 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 That's a lousy, unreadable layout for your SQL :-) There's no SUM in there. When you have a SUM and some non-summed columns (more generally, a mixture of aggregates and non-aggegates), you need a GROUP BY clause listing the non-aggregated columns, and when you apply a condition to an aggregate, you need a HAVING clause. This is standard SQL stuff. SELECT inv1.in1_code, inv1.in1_desc_f, SUM(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' GROUP BY inv1.in1_code, inv1.in1_desc HAVING SUM(inv2.in2_qte_en_main) = 0 ORDER BY inv1.in1_code; I'm worried about that condition on 'inv2.in2_gte_en_main = 0' in the WHERE clause. It seems to me that if that's present, your sum is always going to be zero, so the grouping etc is redundant. I suspect, though, it is a hangover from an unsuccessful experiment. Since you're using ODBC, the barbarous {fn ...} notation is OK, I suppose, but it sure as hell looks like a comment to me. Beware of it if you try to test the query with DB-Access - it will be a comment to the server when submitted via DB-Access, and hence you'll get -201 syntax error. I've not investigated, but you might even be able to omit the SUM() from the SELECT line - after all, you know the value is zero for every row returned. One more issue - you won't get anything for rows in inv1 where there are no matching rows in inv2. You have to determine whether that matters. If it does, you need to start getting fancy with (ISO style) outer joins and worrying about what SUM() returns when it is run over a list of all null non-values. You might have to poke at NVL() too. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Thanks for replying. Somehow, it helped because your query eliminates duplicates but I'm not sure about the effeciency of the SUM() function because of this: Table 2 which is prog.inv2 inv2 and has a field named inv2.in2_qte_en_main. I know tha product code 149833 has 2 rows in that rable, 1 instance with inv2.in2_qte_en_main=0 and the second one inv2.in2_qte_en_main=6. So the query still show that code though 0+6 = 6. Unless I miss something (and it's probably the case) there is soemthing there that doesn't work well. To give a picture of what it is, here is details: Table 2 prog.inv2 inv2 contains quantities of product codes which are in the master table prog.inv1 inv1. Actually, table 2 can have up to 5 duplicate line of each product code because we have 5 different stores. So when the system shows a total of let say 5 in stock, this means that store1 can have 2, store2 has 0, store3... Like I said, your query eliminated duplicates (I had 4 rows of the same code at 0) but there is something with the SUM(). I appreciate your help on this. Thanks Steve "Jonathan Leffler" <jleffler@earthlink.net> a 'crit dans le message de news:6djZb.1119$yZ1.84@newsread2.news.pas.earthlink.net... > Steve wrote: > > 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 > > Why? See my comment below about the {comment} notation in Informix > SQL. [This note was added at the last moment - when reviewing the > question before sending the answer.] > > > 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 > > That's a lousy, unreadable layout for your SQL :-) > > There's no SUM in there. When you have a SUM and some non-summed > columns (more generally, a mixture of aggregates and non-aggegates), > you need a GROUP BY clause listing the non-aggregated columns, and > when you apply a condition to an aggregate, you need a HAVING clause. > This is standard SQL stuff. > > SELECT inv1.in1_code, inv1.in1_desc_f, SUM(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' > GROUP BY inv1.in1_code, inv1.in1_desc > HAVING SUM(inv2.in2_qte_en_main) = 0 > ORDER BY inv1.in1_code;> > I'm worried about that condition on 'inv2.in2_gte_en_main = 0' in the > WHERE clause. It seems to me that if that's present, your sum is > always going to be zero, so the grouping etc is redundant. I suspect, > though, it is a hangover from an unsuccessful experiment. > > Since you're using ODBC, the barbarous {fn ...} notation is OK, I > suppose, but it sure as hell looks like a comment to me. Beware of it > if you try to test the query with DB-Access - it will be a comment to > the server when submitted via DB-Access, and hence you'll get -201 > syntax error. > > I've not investigated, but you might even be able to omit the SUM() > from the SELECT line - after all, you know the value is zero for every > row returned. > > One more issue - you won't get anything for rows in inv1 where there > are no matching rows in inv2. You have to determine whether that > matters. If it does, you need to start getting fancy with (ISO style) > outer joins and worrying about what SUM() returns when it is run over > a list of all null non-values. You might have to poke at NVL() too. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ >