sqldb2db2-luw

Splitting Multi Value Column In DB2


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


Solution

  • 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

    fiddle