Re: SQL Select Question

[Luis Rodriguez <luis.rodriguez2@xxxxxxxxx>]

Jeff,

You have to process your field BEFORE it gets handled by the SUM() function. This works (at least for me):

************************
select sum(                 
  (CASE                     
        WHEN SbTypE = 'O'   
        THEN  SBQSHP        
        ELSE -SBQSHP        
  END)                      
)                           
from ........

************************

Also, In this case I would think that using -SBQSHP should be a little faster than multiplying by -1.

Hope this helps.

Regards,

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions


--- On Wed, 10/29/08, midrange-l-request@xxxxxxxxxxxx <midrange-l-request@xxxxxxxxxxxx> wrote:

> SQL Select Question
> Wednesday, October 29, 2008 5:21 PM
> From: "Jeff Young" <>
> To: <>

> _____________
>
> I have a file consisting of the following fields:
> Invoice
> Type
> Item
> Quantity
>
> When the type is 'O', the quantity should be
> treated as a positive number, otherwise as a negative.
> I would like to be able to select the total quantity for a
> specific invoice and item using SQL.
> When I tried to do this using CASE for the Type field, I
> get an error that the field is not valid since it is not in
> my group by.
>
> The SQL that I get an error on is:
> SELECT Case SbTypE WHen 'O' Then SUM(SBQSHP) Else
> SUM(SBQSHP *    
> -1) End FROM VSADETL WHERE SBCmp = 1 and SbInv = 328258 and
> SbCSIT  
> = 'PR 
> PR026'                                     
>
>                   
> Is there a way to do this in one statement?
>
> Thanks,
>  
> Jeff Young 
> Sr. Programmer Analyst
> IBM -e(logo) server Certified Systems Exper - iSeries
> Technical Solutions V5R2 
> IBM  Certified Specialist- e(logo) server i5Series
> Technical Solutions Designer V5R3
> IBM  Certified Specialist- e(logo)server i5Series
> Technical Solutions Implementer V5R3