camelCase UDTF from Neils Liisberg not working on 7.3

I created the UDTF from Neils and it worked on a 7.4 box. (I have included the procedure below.)

I compiled on a 7.3 partition successfully but received an error when I tried to run it about a missing QICU library.

I searched for an installation method for this library and failed to find anything.

I saved this library from another 7.3 partition and restored on the partition where I was testing the UDTF.

Now the functions runs but the results are not correct. Neils provided an example as follows.

-- usecase
values ( qusrsys.camel_case('Saldo beløb'));

On 7.4 this returns ... saldoBeloeb

On 7.3 this returns ... <Result is blank>

I am not seeing any errors in the log.

Visual explain provided no errors.

Any suggestions greatly appreciated.

Thanks
Don

-- UDTF to return a camelcase of a string for column naming purposes

-- Simply paste this gist into ACS SQL and run it to create the UDTF.

-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

-- It is a cool example how far you can go with SQL: Have fun -
-- (C) Niels Liisberg 2021

-- This gist is distributed on an "as is" basis, without warranties
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
create or replace function qusrsys.camel_case (
name varchar(128)
)
returns varchar(128)
no external action
set option output=*print, commit=*none, dbgview = *list
begin
declare temp varchar(128);
declare outString varchar(128);
declare i int;
declare upperNext int;
declare c char(1);

-- First snake-case the name to sanitise it AND it will also works if the string is already snake-case
set temp = lower(name);
set temp = regexp_replace(temp , '[æ]' , 'ae');
set temp = regexp_replace(temp , '[ø@]' , 'oe');
set temp = regexp_replace(temp , '[å]' , 'aa');
set temp = regexp_replace(temp , '[^a-z0-9]' , ' ');
set temp = trim(temp); -- Done here if the name has trailing invalid chars
set temp = regexp_replace(temp , ' +', '_');

-- Columns names can not begin with a digit
if substring(temp , 1 , 1) >= '0' then
set temp = 'x' concat temp;
end if;

set i = 1;
set upperNext = 0;
set outString = '';
while i <= length(temp) do
set c = substr(temp , i ,1);
if c = '_' then
set upperNext = 1;
elseif upperNext = 1 then
set outString = outString || upper(c);
set upperNext = 0;
else
set outString = outString || c;
end if;
set i = i +1;
end while;
return outString;
end;

-- usecase
values ( qusrsys.camel_case('Saldo beløb'));


Brisbane - Sydney - Melbourne


Don Brown

Senior Consultant




P: 1300 088 400




DISCLAIMER. Before opening any attachments, check them for viruses and defects. This email and its attachments may contain confidential information. If you are not the intended recipient, please do not read, distribute or copy this email or its attachments but notify sender and delete it. Any views expressed in this email are those of the individual sender