RE: SQL Help needed -- MIDRANGE-L

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), asopposed to scalar functions like substr() and concat().

A different context helps sometimes - in Query/400 summary screen there are4 or 5 columns we can check off for summaries. One is the MAXIMUM. Thereare 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 asummary one to a detail one. Unless you normally have multiple sales ofthe 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 theRPG 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.