NCLOB to VARChar

[RichJ]

Hi Sir,

We have a replica from MS SQL Server to Snowflake with numerous transform: NCLOB to Varchar.  Datatype in Source db is NVARCHAR (max) hence NCLOB in Qlik, but client in Snowflake uses different lengths for Varchar, e.g., a target table has one column Varchar(1000) and the other column Varchar(5000).  How can we achieve that in Qlik replication since Qlik doesn't support table Transform for NCLOB?

Thanks,

Richard 

[Dana_Baldwin]

Hi @RichJ 

You can set "Limit LOB size" at the task or table level so the data will fit in the target column:

[RichJ]

Thanks for reply.   As I said,  there is a target table which has different varchar lengths, (say) Varchar(1000) and Varchar(5000).  What "Limit LOB size" should be used in that case? 

Furthermore, "Limit LOB size" cannot be used in table level for Snowflake in Azure.

[RichJ]

If Qlik can treat it on the source EP as WSTRING(1000) or WSTRING(5000), things will work out as WSTRING can have table level transformation.

[john_wang]

Hello @RichJ ,

Besides @Dana_Baldwin comment, yes , we can retrieve different length LOBs easily if the source table has PK or Unique Index.

Let's use source_lookup  & substring function for every LOB column. For example in the first column we truncate the VARCHAR(max) to 1000 chars only:

source_lookup('NO_CACHING','dbo','testclob','substring(notes,1,1000)','id=?',$id)

where 'dbo' is the schema name, 'testclob' is the table name, 'notes' is CLOB column, 'id' is the table PK. IN above expression we retrieve the first 1000 chars from the VARCHAR(max) column.

If the table has NO PK nor Unique Index then then we can do that by defining a VIEW which get the desired length LOBs.

I hope it solve the issue.

Regards,

John.

[shashi_holla · Accepted solution]

@RichJ 

Yes, you can have table level transformations, can modify the column data type from NCLOB to STRING(1000) or WSTRING(1000) as required.

If you prefer to have uniform data length on the target then can use Limit LOB size at the task level.

In case you need more details or a working session, please open a support case and we would be happy to help.

[RichJ]

Thanks for  reply.

One can change it to STRING(1000) or WSTRING(1000), but it does not work - you get the same results. 

The task level Limit LOB Size cannot apply because we have numerous NCLOB's with different Lob size in the task.

[shashi_holla]

In Snowflake, there is no CLOB or NCOLB data type, it will all be varchar. We can just control the data length. Also, the data length limit can't exceed 16MB.

Mostly Limit LOB size is the preferred approach, if you want it customized then can modify the data length manually in Snowflake.