UNION of CLOB in DataStage

Pacific007 · Post by **Pacific007** » Mon Jul 16, 2012 11:43 am

I am facing problem while using Column of datatype in source Query of Oracle. I Googled the same and found we cannot use UNION incase of CLOB while we can use UNION ALL, but by using UNION All we will get duplicates. We cannot apply distinct on CLOB column. Is there any way we can do union of CLOB columns in DataStage and get the distinct value. Apart from clob column there are other columns in source Query. When we remove Clob Cloumn from source , our Union Query works fine.

Regards,
Pacific

chulett · Post by **chulett** » Mon Jul 16, 2012 1:58 pm

What database?

ray.wurlod · Post by **ray.wurlod** » Mon Jul 16, 2012 2:42 pm

What if you CAST them as LONG VARCHAR ?

Pacific007 · Post by **Pacific007** » Tue Jul 17, 2012 9:37 am

Apologies...DataBase is Oracle 10g, and I tried with Cast but data got truncated......as of now I changed the Query and removed the Union, but still the Question is open if we have to do such in future......

rameshrr3 · Post by **rameshrr3** » Tue Jul 17, 2012 11:31 am

I assume you can use 2 seperate queries and combine them with a funnel , parse the clob data and remove duplicates from resolved fields if that was ever an option. I really do not know at this moment if you can set the entire clob data column as a key for removing duplicates though . Was the CLOB data plain text or a wellformed XML ?