Re: SQL Select Question

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