a CONCAT_WS-like on #db2fori #ibmi

In July, I left a position as a data manager on SQL Server (and a bit of MySQL) to start as a database developer on… Db2 … for i. I’m now going through a paradigm shift and transfer some knowledge to the platform.

I had a use case for ‘CONCAT_WS‘. Concatenate 0, 1 or more text with a separator. It nicely deals with the separator if any value is NULL (no need for some CONCAT/COALESCE salad)

So I gave it a naive try (how knows…)

create table testdata(
    ref  int         primary key,
    msg1 varchar(50) default null,
    msg2 varchar(50) default null,
    msg3 varchar(50) default null,
    expected varchar(200) default null
) 
;
insert into testdata(ref, msg1, msg2, msg3, expected) 
values 
    (1, 'no match', 'duplicated record', 'conversion error', 'no match | duplicated record | conversion error'),
    (2, 'no match', NULL, 'conversion error', 'no match | conversion error'),
    (3, NULL, NULL, NULL, NULL)
;
select td.ref, td.expected, 
    concat_ws(' | ', td.msg1, td.msg2, td.msg3) as observed
from testdata as td
;

… Failed and no trace of such a function in the documentation. Maybe an alternative trick, then? Use an aggregate function (LISTAGG for current the task) in combination with VALUES and LATERAL JOIN (or Sub-select). Second try:

select td.ref, agg.observed, td.expected
from testdata as td
cross join lateral (
    select list_agg(msg, ' | ') as observed
    from (VALUES (td.msg1), (td.msg2), (td.msg3)) as tlong(msg)) as agg
; 

SQL-Status: 42703
Anbietercode: -206
Nachricht: [SQL0206] Column or global variable MSG3 not found. Cause . . . . . :   MSG3 was not found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, MSG3 is not a column of any table or view that can be referenced, or MSG3 is a special register that cannot be set in an atomic compound statement. Recovery  . . . :   Do one of the following and try the request again: — Ensure that the column and table names are specified correctly in the statement. — If this is a SELECT statement, ensure that all the required tables were named in the FROM clause. — If the column was intended to be a correlated reference, qualify the column with the correct table designator. — If the column was intended to be a global variable, qualify the name with the schema where the global variable exists or ensure the schema is in the path. — If this is a SET statement for a special register within an atomic compound dynamic statement, remove the statement or remove the ATOMIC keyword.

Nope! I did qualify my correlated columns, so why do you Db2 block it in a LATERAL? Some limitation? But with some patience, I went for an UDFs (function code at the end):

with test as (
    select td.ref, td.expected, 
        concat_ws_allownull(' | ', td.msg1, td.msg2, td.msg3) as observed
    from testdata as td
)
select test.ref, test.expected, test.observed, 
    case when test.expected is not distinct from test.expected
        then 'Succeed' else 'Failed' end as test_result
from test
;

Yeah!

Not quite the real CONCAT_WS, but I learned a bit more about SQL on Db2 for i, had some fun and now have this tool back.


CREATE OR REPLACE FUNCTION CONCAT_WS_ALLOWNULL ( 
	SEP VARCHAR(10) , 
	S1 VARCHAR(4000) , 
	S2 VARCHAR(4000) DEFAULT  NULL  , 
	S3 VARCHAR(4000) DEFAULT  NULL  , 
	S4 VARCHAR(4000) DEFAULT  NULL  , 
	S5 VARCHAR(4000) DEFAULT  NULL  ) 
	RETURNS VARCHAR(4000)   
	LANGUAGE SQL 
	SPECIFIC CONWSALNUL 
	DETERMINISTIC  
BEGIN   
    RETURN ( 
        SELECT LISTAGG ( Z . TXT, SEP ) 
        FROM ( VALUES 
            ( S1 ) , ( S2 ) , ( S3 ) , ( S4 ) , ( S5 ) 
            ) AS Z ( TXT ) 
        WHERE Z.TXT IS NOT NULL 
         /* This WHERE statement helped with  
         * a CEE9901 Error due to too 
         * many message caused by NULLs on *listagg* 
         * input (related with SQL State 01003) 
         * */ 
    ) ; 
END  ; 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: