topic Re: DB2 - How to use transform expression with special character in column name in Qlik Replicate

DB2 - How to use transform expression with special character in column name

amontalbano — Mon, 21 Feb 2022 14:08:42 GMT

I'm attempting to move data from a DB2 table on an IBM i to MSSQL through a log stream.

The MSSQL side requires a derived column combining two columns. The problem is the expression builder won't allow me to use the name of the columns coming from the log stream due to a special character in the name.

I have columns: meemem# and meedep#. I need combine these 2 columns into a single column with the expression -

(($meemem# * 100) + $meedep#). The expression builder balks at this with the error, 'SQLite general error. Code <1>, Message <unrecognized token: "#">.'

I'm able to change the column names in the output, so I would have to believe there's a way of making this work. Any suggestions?

I've already tried:

(([$meemem#] * 100) + [$meedep#]) - SQLite error - No such column: $meemem#
(("$meemem# "* 100) + "$meedep#") - returns 0 for all and doesn't show in parse expression
(('"$meemem#" * 100) + '"$meedep#"') - returns 0 for all and doesn't show in parse expression
(($meememNum * 100) + $meedepNum) - renamed column in transform. Expression builder works, but fails when the task is run. - Failed to init column calculation expression '(($MEMEMNUM * 100) + $MEDEPNUM)' (replicationtask.c:3063)

Re: DB2 - How to use transform expression with special character in column name

Heinvandenheuvel — Mon, 21 Feb 2022 14:11:10 GMT

Good question. I suspect this is a restriction, or possibly a bug, in the Qlik Replicate transformation expressions.

Replicate uses SQLite to perform the expression. SQLite can handle funky identifier names double-quotes, and optional square bracket [] or even back-ticks ( https://www.sqlite.org/lang_keywords.html ) but none of those are accepted in the Replicate expression builder all leading to Message <unrecognized token: "$">

I also tried round brackets () and curly brackets (braces) {}. No joy.

I recommend a support ticket for fix or enhancement.

Now these funky characters, and even just spaces, always create trouble all the time and are really best avoided. Often the source database tables are cast (sic) in concrete and cannot be changed without breaking the main (source) application. Maybe you can fix this on the target though replacing # by '_NR' or similar.

Can you use a VIEW, with better names, instead of the base table?

Can you use a synonym or alias on the source somehow (I don't know DB2).

hth,

Hein

Re: DB2 - How to use transform expression with special character in column name

amontalbano — Mon, 21 Feb 2022 14:28:10 GMT

Thank you for the insight. I do not have the ability to change the source naming. Because I'm asked to use Log Streams, I'm not able to change the column name in the process of receiving the DB2 data. So it leaves me kind of stuck. I could probably do something to run over the MSSQL and populate the column after the data is replicated.

I'll look into whether or not I can get the DB2 side to create a view or alias.

Thank you!!

Re: DB2 - How to use transform expression with special character in column name

john_wang — Tue, 22 Feb 2022 01:12:52 GMT

Hello @amontalbano , copy @Heinvandenheuvel ,

This is an known limitation Transformations cannot be performed on columns that contain special characters and there is Feature Request in an Idea however looks to me it's not in the roadmap yet.

Transformations cannot be performed on columns that contain special characters (e.g. #, \, /, -) in their name.

Hope this helps.

Regards,

John.