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…)
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:
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):
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.
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 ;
