Page 1 of 1
UNION of CLOB in DataStage
Posted: Mon Jul 16, 2012 11:43 am
by Pacific007
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
Posted: Mon Jul 16, 2012 1:58 pm
by chulett
What database?
Posted: Mon Jul 16, 2012 2:42 pm
by ray.wurlod
What if you CAST them as LONG VARCHAR ?
Posted: Tue Jul 17, 2012 9:37 am
by Pacific007
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......
Posted: Tue Jul 17, 2012 11:31 am
by rameshrr3
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 ?