topic Adding multiple new columns with SQLite transformation fails in Qlik Replicate

Adding multiple new columns with SQLite transformation fails

JGRFCU — Tue, 05 Mar 2024 23:20:05 GMT

I am trying to add two new string columns (Column A and Column B) to a table using the Transform tab. Both column transformations use SQLite function to parse the data. The task fails with a generic SQLite error: "SQLite general error. Code <25>, Message <column index out of range>."

However, if I only add one new column the task runs correctly. It is only when I try to add more than one new column, and both are using SQLite functions, that I get this error message. I don't believe it is a data issue because I individually tested adding only Column A successfully and then only Column B successfully. I also tried adding Column A with the SQLite transform and Column B as just a literal string value (no SQLite) and this worked. I even tried using different SQLite functions for each column and it still fails when adding both. Always the same general error.

Is there any insight to what might be happening here?

Thank you!

SQL Server source. Snowflake target.

Re: Adding multiple new columns with SQLite transformation fails

Michael_Litz — Tue, 05 Mar 2024 23:38:19 GMT

Hi @JGRFCU

Please post the SQLite functions you used. I would like to try and reproduce this.

Thanks,
Michael

Re: Adding multiple new columns with SQLite transformation fails

JGRFCU — Tue, 05 Mar 2024 23:59:38 GMT

Hi Michael,

The original functions are this: strftime('%Y-%m', $ColumnA); and strftime('%Y-%m', $ColumnB);

I am parsing the year and month (YYYY-mm) from two date columns into new string columns.

Thanks!

Re: Adding multiple new columns with SQLite transformation fails

Michael_Litz — Wed, 06 Mar 2024 00:12:01 GMT

Hi @JGRFCU

I just tested in my task and this worked.

Is there anything else that may be different?

Thanks,

Michael

Re: Adding multiple new columns with SQLite transformation fails

DesmondWOO — Wed, 06 Mar 2024 02:01:39 GMT

Hi @JGRFCU ,

I've tested on SQL server also, and it works successfully for both Full Load and CDC.

Could you give us following information?
1. Source database and Target database
2. DDL of the source table (please rename table and column names if data security is a concern)
3. Sample data

Thanks,
Desmond

Re: Adding multiple new columns with SQLite transformation fails

Heinvandenheuvel — Wed, 06 Mar 2024 04:05:27 GMT

@DesmondWOO , @Michael_Litz You guys cannot read!

>>> The original functions are this: strftime('%Y-%m', $ColumnA); and strftime('%Y-%m', $ColumnB);

See that semicolon? I bet you didn't test with that, did you now?

@JGRFCU This is surely a very silly - slap against the forehead - error. What made you decide to put that semicolon on the end? It is not supposed to be there and unfortunately is not flagged during test or with a single transformation, but with two it blows up.

Actually in my test (oracle source) the task crashed and did NOT report the error indicated but nevertheless I'm very sure that the semicolons at the end of the transformations cause the issue.

Hein.

Re: Adding multiple new columns with SQLite transformation fails

JGRFCU — Wed, 06 Mar 2024 14:43:43 GMT

Wow thank you, very much a facepalm moment lol. It certainly was the semicolons that were the issue. When I was looking up SQLite syntax, as I am very much a novice, all the examples ended with a semicolon and I didn't see anything in the Replicate documentation about not using them. Didn't think they would be problematic, obviously I was wrong. I've removed them and everything is running smoothly.

Thank you again @Heinvandenheuvel !

Also thank you @DesmondWOO and @Michael_Litz for your time and effort.

Re: Adding multiple new columns with SQLite transformation fails

Heinvandenheuvel — Sun, 10 Mar 2024 18:56:51 GMT

Fwiw, I do think this is worth a support ticket, internally or by a customer. It is both a runtime bug and design time weakness. It was readily spotted this time but could be more tricky under other circumstances such as in my test case silently crashing the task process

Re: Adding multiple new columns with SQLite transformation fails

DesmondWOO — Mon, 11 Mar 2024 03:29:04 GMT

Hi @JGRFCU ,

I can reproduce the same problem on the SQL server that you mentioned in the case. As @Heinvandenheuvel mentioned before, please create a support ticket. We will follow up with the R&D team.

Regards,
Desmond