Re: SQL question

Hi Paul,

I'm no expert by any means and there's probably a nicer solution (maybe
using a CTE?) but this worked for me. I wasn't sure what your key is but
assuming it's the first four columns...

update ty2temp x
set x.positive =
case x.flag
when '1' then (select y.quantity from ty2temp y where x.acct=y.acct and
x.brch=y.brch and x.tyc=y.tyc and x.yrm=y.yrm)
when '2' then 0
end,
x.negative =
case x.flag
when '1' then 0
when '2' then (select y.quantity from ty2temp y where x.acct=y.acct and
x.brch=y.brch and x.tyc=y.tyc and x.yrm=y.yrm)
end;

Yours truly,

Glenn Gundermann
Email: ggundermann@xxxxxx
Cell: (416) 317-3144


Subject: SQL question
From: "A Paul" <amandapaul1@xxxxxxxxxxx>
Date: Mon, September 16, 2013 6:44 pm
To: "RPG programming on the IBM i \(AS/400 and iSeries\)"
<rpg400-l@xxxxxxxxxxxx>

Hi
I am looking for SQL to add additional two columns(positive and negative)
to my current file and populate them as below.
Any quick help is appreciated.
 
positive = QUANTITY WHEN FLAG =1
negative = 0       WHEN FLAG  =1
positive = 0       WHEN FLAG =2
negative = QUANTITY WHEN FLAG =2
 
CURRENT:
======ACCT brch TYC     yrm     flag QUANTITY
ABCPQ 1     21     201312 2     645   
ABCPQ 1     25     201312 1     1920   
ABCPQ 1     26     201312 2     192   
1234C 1     25     201312 1     144   
       
       
EXPECTED:
========       
       
ACCT     brch TYC yrm     flag QUANTITY positive negative
             
             
ABCPQ     1     21 201312     2  645       0      645
ABCPQ     1     25 201312     1  1920      1920    0
ABCPQ     1     26 201312     2  192          0    192
1234C     1     25 201312     1  144       144     0--