String truncation during transformation

[sergsyb]

I am sorry for may be trivial question, but in which way I can do transformation and truncate original string.
For example

On the Source we have column with text datatype. (very long string)
On the Target I would like to truncate original long string and fit in char(20) for example

If I only define new datatype for this column STRING (20) in Output of Transorm tab, new table with column char(20) creates on the target, but data for FULL LOAD contain long original string. As result target get error - original string could not fit in char(20)

Which expression i should add to transformation to truncate original long string to 20 character?

[john_wang]

Hello @sergsyb ,

Please allow me some time, Let me see if we can find a workaround for it or not.

Will get back to you later.

Regards,

John.

[john_wang · Accepted solution]

Hello @sergsyb ,

I think the most easy way is using MATERIALIZED VIEW, we cut the CLOB column to 20 chars in the view, for example:

  create MATERIALIZED VIEW public.testtext_vw as 
  select id,name,notes,substring(notes from 1 for 20) from public.testext;
  REFRESH MATERIALIZED  VIEW public.testtext_vw;

Unfortunately PostgreSQL Logical Replication does not support  MATERIALIZED VIEW. See ...must be normal tables, not views, materialized views... .

So we have to get back to Replicate again. Let's use source_lookup function. In your current transformation, change the expression to

source_lookup('NO_CACHING','public','testext','substring(notes from 1 for 20)','id=?',$id)

where 'public' is the schema name, 'testtext' is the table name, 'notes' is CLOB column, 'id' is the table PK.

I hope it solve the issue.

Regards,

John.

[Michael_Litz · Accepted solution]

Hi @sergsyb ,

Please have a look at this source lookup function as it will do a lookup into the source field and it should return the LOB value as string and then the substr function should work. 

Transformation: Source Lookup - Oracle ROWID 5/4 ( Ted )
https://community.qlik.com/t5/Knowledge/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170

Please let me know if this helps out.

Thanks,
Michael

[sergsyb]

Hi John, Michael

You are simply the best. Thanks a lot for your suggestion re Lookup  function.

Finally, with help of this function we managed to successfully replicate truncated LOB value to the target..