Getting distinct values from a column of data type CLOB
-- What I have learnt today:
bkd@Oracle>drop table t;
Table dropped.
bkd@Oracle>create table t
2 as
3
bkd@Oracle>create table t (col clob);
Table created.
bkd@Oracle>insert into t
2 (select 'A' from dual
3 UNION ALL
4 select 'B' from dual
5 union all
6 select 'C' from dual
7 union all
8 select 'A' from dual );
4 rows created.
bkd@Oracle>select col from t;
COL
--------------------------------------------------------------------------------
A
B
C
A
bkd@Oracle>--Remove duplicate--
bkd@Oracle>select distinct col from t;
select distinct col from t
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
You can not use DISTINCT in column of data type CLOB.So, how we are going to get the distinct values out of it?
Workaround:
bkd@Oracle>select a.col from t a
2 where not exists ( select 1 from t b
3 where a.rowid < b.rowid
4 and dbms_lob.compare(a.col,b.col)=0);
COL
--------------------------------------------------------------------------------
B
C
A
Regards
BKD
-- What I have learnt today:
bkd@Oracle>drop table t;
Table dropped.
bkd@Oracle>create table t
2 as
3
bkd@Oracle>create table t (col clob);
Table created.
bkd@Oracle>insert into t
2 (select 'A' from dual
3 UNION ALL
4 select 'B' from dual
5 union all
6 select 'C' from dual
7 union all
8 select 'A' from dual );
4 rows created.
bkd@Oracle>select col from t;
COL
--------------------------------------------------------------------------------
A
B
C
A
bkd@Oracle>--Remove duplicate--
bkd@Oracle>select distinct col from t;
select distinct col from t
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
You can not use DISTINCT in column of data type CLOB.So, how we are going to get the distinct values out of it?
Workaround:
bkd@Oracle>select a.col from t a
2 where not exists ( select 1 from t b
3 where a.rowid < b.rowid
4 and dbms_lob.compare(a.col,b.col)=0);
COL
--------------------------------------------------------------------------------
B
C
A
Regards
BKD
No comments:
Post a Comment