Skip to main content

Qlik Replicate

Discussion board for collaboration on Qlik Replicate.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
amontalbano
Contributor II
Contributor II

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

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)
Labels (1)
1 Solution

Accepted Solutions
john_wang
Support
Support

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.

View solution in original post

3 Replies
Heinvandenheuvel
Creator III
Creator III

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

 

 

amontalbano
Contributor II
Contributor II
Author

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

john_wang
Support
Support

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.