RE: SQL Help needed -- MIDRANGE-L

I am trying the max function right now and see if that will work in the 
context of the report....if it does I'll advise.....

On Thu, 18 Aug 2005 08:09:13 -0500, Vernon Hamberg wrote
> Hi Charles
> 
> Since Douglas needs the last date, the summary functions are still 
> usable. Peter mentioned the MAX() function - that is one of the 
> summary functions. In the SQL manual I think these are called column 
> functions (IIRC), as opposed to scalar functions like substr() and 
> concat().
> 
> A different context helps sometimes - in Query/400 summary screen 
> there are 4 or 5 columns we can check off for summaries. One is the 
> MAXIMUM. There are similar functions in SQL for each column - TOTAL, 
> SUM, AVG, MAX, MIN, even STDDEV, IIRC, which is not in Query/400.
> 
> Vern
> 
> At 07:24 AM 8/18/2005, you wrote:
> 
> >Douglas,
> >
> >It seems that the requirement for date sold switches the query from a 
> >summary one to a detail one.  Unless you normally have multiple sales of 
> >the same item to the same customer on the same day.
> >
> >Thus, you're going to have to move the summary stuff from the query to 
the 
> >RPG program itself.
> >
> >C/EXEC SQL
> >C+    DECLARE DETAILCURSOR CURSOR
> >C+    FOR SELECT
> >C+        ILNINV#A,
> >C+        ILNSOLDTO,
> >C+        ILNINVDAT,
> >C+        ICMNAME,
> >C+        ILNSTK,
> >C+        ILNDESC,
> >C+        ILNEPRICE,
> >C+        ILNEUAVCST,
> >C+        ILNEPRICE - ILNEUAVCST AS MARGIN
> >C+    FROM INVMARGIN
> >C+    WHERE
> >C+        ILNINV#A = :ALPHPARM AND
> >C+        ILNINVDATE >= :SDATE AND ILNINVDATE <= :EDATE
> >C+    ORDER BY
> >C+        ILNINV#A,
> >C+        ILNSOLDTO,
> >C+        MARGIN DESC
> >C/END-EXEC
> >
> >  /free
> >     //  code to handle breaks
> >     wTotalPrice += ILNEPRICE;
> >     wTotalCost += ILNEUAVCST;
> >     wTotalMargin += MARGIN;
> >     // other code as required
> >  /end-free
> >
> >
> >
> >Charles Wilt
> >--
> >iSeries Systems Administrator / Developer
> >Mitsubishi Electric Automotive America
> >ph: 513-573-4343
> >fax: 513-398-1121
> >
> >
> > > -----Original Message-----
> > > From: midrange-l-bounces@xxxxxxxxxxxx
> > > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Douglas W. Palme
> > > Sent: Thursday, August 18, 2005 8:13 AM
> > > To: Mid Range List
> > > Subject: SQL Help needed
> > >
> > >
> > > I need some serious sql help.  I have a program that goes
> > > through our line
> > > item sales data and produces a report on a customer by customer basis
> > > detailing the summary data for the line items, IE: total
> > > sales, total cost,
> > > margins, etc.  However, it meeting with the sales staff last
> > > week, they want
> > > to know when each item was last purchased.  Frankly I have no idea
> > > whatsoever of how to include that in the statement.
> > >
> > > Here is what I have right now:
> > >
> > > C/EXEC SQL
> > > C+    DECLARE DETAILCURSOR CURSOR
> > > C+    FOR SELECT
> > > C+        ILNINV#A,
> > > C+        ILNSOLDTO,
> > > C+        ICMNAME,
> > > C+        ILNSTK,
> > > C+        ILNDESC,
> > > C+        SUM(ILNEPRICE),
> > > C+        SUM(ILNEUAVCST),
> > > C+        SUM(ILNEPRICE) - SUM(ILNEUAVCST) AS MARGIN
> > > C+    FROM INVMARGIN
> > > C+    WHERE
> > > C+        ILNINV#A = :ALPHPARM AND
> > > C+        ILNINVDATE >= :SDATE AND ILNINVDATE <= :EDATE
> > > C+    GROUP BY
> > > C+        ILNINV#A,
> > > C+        ILNSOLDTO,
> > > C+        ICMNAME,
> > > C+        ILNSTK,
> > > C+        ILNDESC
> > > C+    ORDER BY
> > > C+        ILNINV#A,
> > > C+        ILNSOLDTO,
> > > C+        MARGIN DESC
> > > C/END-EXEC
> > >
> > > There is a field included in the file called ILNINVDATe which
> > > is the sale
> > > date, but I am only using that field thus far for record
> > > selection....any
> > > ideas or suggestions would be appreciated.
> > >
> > >
> > >
> > > If you bought, it was hauled by a truck - somewhere, sometime.
> > >
> > > --
> > > This is the Midrange Systems Technical Discussion
> > > (MIDRANGE-L) mailing list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> > >
> > >
> >
> >--
> >This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
> >To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> >To subscribe, unsubscribe, or change list options,
> >visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >or email: MIDRANGE-L-request@xxxxxxxxxxxx
> >Before posting, please take a moment to review the archives
> >at http://archive.midrange.com/midrange-l.
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/midrange-l or email: 
> MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment 
> to review the archives at http://archive.midrange.com/midrange-l.


If you bought, it was hauled by a truck - somewhere, sometime.