Re: Multitable select with two seperate search criteria.

>From: macphee@conan.ids.net (Donald Mac Phee Ii) >Date: 29 Apr 1997 13:51:07 GMT >X-Informix-List-Id: <news.37264> > >table1 = >Itemlink (serial) >DollarAmt (float) > >table2 = >Date (Date) >Detaillink (serial) [Corresponds to Itemlink] >Accode (char) > >table3 = >Accode (char) >Address (char) > >This is an oversimplification, but will do for the purposes of my >question. > >Itemlink is a nonunique number and can have multiple entries in table1. If Itemlink can have duplicate entries, why is it a SERIAL? It should be an INTEGER, especially since it always contains values determined by the SERIAL column in Table2 (which is, correctly, a SERIAL column). All else apart, it is misleading -- a SERIAL column should always have a unique index on it, and should usually be the primary key for a table. Rule of thumb: do not use SERIAL if the column will only hold values determined by some other table. >Detaillink is unique in table two. > >What I want to do is get the sum of all the DollarAmt by account for two >distinct ranges of date, compare them, and output the results of the >comparison. > >Or more definitively: Produce a report, sorted by account, summing all >dollar activity for week1, and week2; comparing all of the totals and >producing a third value for percentage change, and then output the results >in a column format listing Accode, address, week1, week2, %change OK, assume that we can define s_week1 as the start date for week1, and e_week1 as the end date, and corresponding values for week2. -- Get values for week 1 into temp table X1 SELECT Accode, SUM(DollarAmt) AS Week1 FROM Table1, Table2 WHERE Table1.Itemlink = Table2.Detaillink AND Table2.Date BETWEEN s_week1 AND e_week1 INTO TEMP X1; -- Get values for week 2 into temp table X2 SELECT Accode, SUM(DollarAmt) AS Week2 FROM Table1, Table2 WHERE Table1.Itemlink = Table2.Detaillink AND Table2.Date BETWEEN s_week2 AND e_week2 INTO TEMP X2; -- Consider indexing X1 and X2 on Accode, but it probably isn't worth -- it (and/or the optimizer will do it for you which doesn't matter since -- you are only using the indexes once). -- Get the required output data from the tables which hold it SELECT Table3.Accode, Table3.Address, X1.Week1, X2.Week2, ROUND(((X2.Week2 - X1.Week1) * 100.0)/X1.Week1, 2) AS "%Change" FROM Table3, X1, X2 WHERE Table3.Accode = X1.Accode AND Table3.Accode = X2.Accode AND X1.Accode = X2.Accode;-- The third join is logically redundant, but gives the optimizer the maximum -- opportunity to optimize. Experiment to see whether it helps. DROP TABLE X1; DROP TABLE X2; This only needs DB-Access or ISQLRF (Query Language option). >Seeing as how I only have the runtime of SQL, and no compiled language, it >must be done using isqlrf, biff, awk, csh, sh, etc... biff reports when mail arrives?!? etc is a directory? :-) >My first few attempts have yielded promising results, but I can't seem to >make awk process all of those rows, and I'm pretty sure there must be an >easier way of accomplishing this using ISQL... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>