Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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
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..