Thanks for the below SQL I was able to add two rows by using CASE statement, your help is very appreciated. If I have a record as below
ACCT brch TYC yrm flag QUANTITY positive negative
234C 1 25 201312 1 144 144 255
how would I split it into two as below :
ACCT brch TYC yrm flag QUANTITY positive negative
234C 1 25 201312 1 144 144 0
234C 1 25 201312 2 144 0 255
Basically the flag becomes 2 when there is negative value, and flag is 1 when there is positive value.
________________________________
From: CRPence <CRPbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Sent: Wednesday, 18 September 2013, 16:31
Subject: 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"
midrange.com
