RE: SQL Help needed

Vern,

You're correct, I missed the "last" in the original post.

Thus Douglas this should work assuming that the date field is either a true 
date field or one in a sortable (Year-Month-Day) form.

C/EXEC SQL                                          
C+    DECLARE DETAILCURSOR CURSOR                   
C+    FOR SELECT                                    
C+        ILNINV#A,                                 
C+        ILNSOLDTO,                                
C+        ICMNAME,                                  
C+        ILNSTK,                                   
C+        ILNDESC,   
C+        MAX(ILNINVDATE),                               
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           

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 Vernon Hamberg
> Sent: Thursday, August 18, 2005 9:09 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL Help needed
> 
> 
> 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:
>