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
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
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 ;
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 ;
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 ;