Re: SQL question

On 16 Sep 2013 15:44, A Paul wrote:

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

Seems nothing about this [very broadly entitled] topic has any relationship to, nor any dependence upon, the RPG [host] language.

Regardless, the following two statements, the ALTER and the UPDATE, should suffice; explicitly consider which default and null-ability:

alter table the_table
add column positive integer /* [not null] default 0 */
add column negative integer /* [not null] default 0 */
;
-- the following update to all rows setting each new column:
update the_table
set positive = case flag when 2 then 0 when 1 then QUANTITY end
, negative = case flag when 1 then 0 when 2 then QUANTITY end
; -- Note: NULL is implied; i.e. if flag NOT IN(1 , 2) then NULL
-- Note: a WHERE clause could could ensure FLAG IN (1, 2)

Any of the following SQL [each preceded by a blank line and double-dash comment line(s)] could replace the above UPDATE:

-- the following is just alternative syntax to the prior:
update the_table
set ( positive, negative ) =
( case flag when 2 then 0 when 1 then QUANTITY end
, case flag when 1 then 0 when 2 then QUANTITY end
) /* same NULL note as above; same WHERE note also */
;

-- or as two separate UPDATE statements
-- if alter effected default 0, then rmv 2nd assignment in each
update the_table
set positive = QUANTITY
, negative = 0
where flag=1
;
update the_table
set negative = QUANTITY
, positive = 0
where flag=2
;

-- or using the MERGE statement could be an option
-- AFaIK the syntax should be accurate; I can not test
MERGE INTO the_table target
USING ( select * from the_table ) source
/* perhaps replace above * with key columns and QUANTITY */
ON target.key = source.key
/* AND ... add predicate for all keys to uniquely match a row */
WHEN MATCHED AND target.flag = 1 THEN
UPDATE SET target.positive = source.QUANTITY
, target.negative = 0
WHEN MATCHED AND target.flag = 2 THEN
UPDATE SET target.positive = 0
, target.negative = source.QUANTITY
; -- if alter defaulted zero, then optionally omit each "= 0"