RE: SQL Help needed

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

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.
> 
>