× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.