I have a db2 table that stored a balance movement and it has a multi-value column in it. The multi-value is separated by "ý" character. The table is something like this
ID CURR_ASSET_TYPE OPEN_BALANCE DEBIT_MVMT CREDIT_MVMT
0023216531 CREDITýCREDIT 315246ý ý-315246 ý315246
0023216540 CREDITý50000ý50000ýCREDITý50000 2173118.69ý432ýýý ýýýý-461.69 ýý14.85ý461.69ý14.84
0023216558 DEBITýDEBIT -5000ý ý ý5000
0023216477 CREDITý50000ý50000ýCREDITý50000 121667.58ý25.14ýýý ýýýý-26.8 ýý0.83ý26.8ý0.83
I need to split the multi-value column per id so it become like this, I will take ID 0023216477 as example
ID CURR_ASSET_TYPE OPEN_BALANCE DEBIT_MVMT CREDIT_MVMT
0023216477 CREDIT 121667,58 0 0
0023216477 50000 25,14 0 0
0023216477 50000 0 0 0,83
0023216477 CREDIT 0 0 26,8
0023216477 50000 0 -26,8 0,83
I have been searching for clue online, but I can't seems find the function or method that works for me. I'm using db2 - LUW (8.x/9.x/10.x/11.x) for Windows and it doesn't support SPLIT function. Is there anything that I can try?
This is my dbfiddle: https://dbfiddle.uk/Rq7K5PYC
A little bit shorter & simpler solution.
We use seq for ordinality column definition instead of each row enumeration with an OLAP functions.
xmlpath expressions for convenient string -> decimal data type conversion is used as well.
WITH multivalue (ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT) AS(
VALUES
('0023216531', 'CREDITýCREDIT', '315246ý', 'ý-315246', 'ý315246'),
('0023216540', 'CREDITý50000ý50000ýCREDITý50000', '2173118.69ý432ýýý', 'ýýýý-461.69', 'ýý14.85ý461.69ý14.84'),
('0023216558', 'DEBITýDEBIT', '-5000ý', 'ý', 'ý5000'),
('0023216477', 'CREDITý50000ý50000ýCREDITý50000', '121667.58ý25.14ýýý', 'ýýýý-26.8', 'ýý0.83ý26.8ý0.83')
)
SELECT
m.id
, c1.seq
, c1.tok as CURR_ASSET_TYPE
, coalesce(c2.tok, 0) as OPEN_BALANCE
, coalesce(c3.tok, 0) as DEBIT_MVMT
, coalesce(c4.tok, 0) as CREDIT_MVMT
FROM
multivalue m
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>'
passing
m.CURR_ASSET_TYPE as "s"
columns
seq for ordinality
, tok varchar(20) path '.'
) c1
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>'
passing
m.OPEN_BALANCE as "s"
columns
seq for ordinality
, tok decfloat path 'if (. castable as xs:decimal) then xs:decimal(.) else ()'
) c2
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>'
passing
m.DEBIT_MVMT as "s"
columns
seq for ordinality
, tok decfloat path 'if (. castable as xs:decimal) then xs:decimal(.) else ()'
) c3
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>'
passing
m.CREDIT_MVMT as "s"
columns
seq for ordinality
, tok decfloat path 'if (. castable as xs:decimal) then xs:decimal(.) else ()'
) c4
WHERE
c2.seq = c1.seq
and c3.seq = c1.seq
and c4.seq = c1.seq
order by 1, 2
| ID | SEQ | CURR_ASSET_TYPE | OPEN_BALANCE | DEBIT_MVMT | CREDIT_MVMT |
|---|---|---|---|---|---|
| 0023216477 | 1 | CREDIT | 121667.58 | 0 | 0 |
| 0023216477 | 2 | 50000 | 25.14 | 0 | 0 |
| 0023216477 | 3 | 50000 | 0 | 0 | 0.83 |
| 0023216477 | 4 | CREDIT | 0 | 0 | 26.8 |
| 0023216477 | 5 | 50000 | 0 | -26.8 | 0.83 |
| 0023216531 | 1 | CREDIT | 315246 | 0 | 0 |
| 0023216531 | 2 | CREDIT | 0 | -315246 | 315246 |
| 0023216540 | 1 | CREDIT | 2173118.69 | 0 | 0 |
| 0023216540 | 2 | 50000 | 432 | 0 | 0 |
| 0023216540 | 3 | 50000 | 0 | 0 | 14.85 |
| 0023216540 | 4 | CREDIT | 0 | 0 | 461.69 |
| 0023216540 | 5 | 50000 | 0 | -461.69 | 14.84 |
| 0023216558 | 1 | DEBIT | -5000 | 0 | 0 |
| 0023216558 | 2 | DEBIT | 0 | 0 | 5000 |