Home » SQL & PL/SQL » SQL & PL/SQL » How to use UNION with a CLOB [split topic]
How to use UNION with a CLOB [split topic] [message #395408] Wed, 01 April 2009 06:02 Go to next message
m2c_analyst
Messages: 4
Registered: March 2009
Junior Member
Hi,
I am trying to create a view where a column is a CLOB column but I having a problem.

My view is :

CREATE MATERIALIZED VIEW vw_ticket_faq_favoritos

AS
(SELECT fav.fav_id AS ID, inc.tic_id AS IDOBJETO, fav.fav_fcreacion AS FCREACION, fav.fav_usuario AS USUARIO,
inc.tic_descripcion AS DESCRIPCION, fav.fav_desobjeto AS TIPO,inc.tic_respuesta AS RESPUESTA
FROM inc_ticket inc, inc_favoritos fav
WHERE fav.fav_idobjeto = inc.tic_id(+)
AND fav.fav_desobjeto LIKE 'INCIDENCIA'
UNION
SELECT fav.fav_id AS ID, pre.pre_id AS IDOBJETO, fav.fav_fcreacion AS FCREACION, fav.fav_usuario AS USUARIO,
pre.pre_pregunta AS DESCRIPCION, fav.fav_desobjeto,pre.pre_respuesta AS RESPUESTA
FROM faq_preguntas pre, inc_favoritos fav
WHERE fav.fav_idobjeto = pre.pre_id(+) AND fav.fav_desobjeto LIKE 'FAQ')


But I have this oracle error :

ORA-00932.

Both inc.tic_respuesta and pre.pre_respuesta are CLOB columns so I do not understand what the problem is.

Thanks in advanced

[Mod-Edit: Frank changed title to more descriptive one]

[Updated on: Thu, 02 April 2009 02:40] by Moderator

Report message to a moderator

Re: (split from unrelated hijacked thread in wrong forum) [message #395530 is a reply to message #395408] Wed, 01 April 2009 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

SELECT fav.fav_id          AS id, 
       inc.tic_id          AS idobjeto, 
       fav.fav_fcreacion   AS fcreacion, 
       fav.fav_usuario     AS usuario, 
       inc.tic_descripcion AS descripcion, 
       fav.fav_desobjeto   AS tipo, 
       inc.tic_respuesta   AS respuesta 
FROM   inc_ticket inc, 
       inc_favoritos fav 
WHERE  fav.fav_idobjeto = inc.tic_id (+) 
       AND fav.fav_desobjeto LIKE 'INCIDENCIA' 
UNION 
SELECT fav.fav_id        AS id, 
       pre.pre_id        AS idobjeto, 
       fav.fav_fcreacion AS fcreacion, 
       fav.fav_usuario   AS usuario, 
       pre.pre_pregunta  AS descripcion, 
       fav.fav_desobjeto, 
       pre.pre_respuesta AS respuesta 
FROM   faq_preguntas pre, 
       inc_favoritos fav 
WHERE  fav.fav_idobjeto = pre.pre_id (+) 
       AND fav.fav_desobjeto LIKE 'FAQ'


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:67082879150052

[Updated on: Wed, 01 April 2009 15:04]

Report message to a moderator

Re: (split from unrelated hijacked thread in wrong forum) [message #395534 is a reply to message #395408] Wed, 01 April 2009 15:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You cannot use distinct on a clob. Using union does a distinct sort, so you cannot use union with a clob column. You can use union all, since it does not do a distinct sort, but you may have duplicates. Please see the demo below.

-- test environment:
SCOTT@orcl_11g> create table tab1 (col1 clob)
  2  /

Table created.

SCOTT@orcl_11g> insert into tab1 values ('test1')
  2  /

1 row created.

SCOTT@orcl_11g> create table tab2 (col2 clob)
  2  /

Table created.

SCOTT@orcl_11g> insert into tab2 values ('test2')
  2  /

1 row created.


-- distinct not allowed on clob column:
SCOTT@orcl_11g> select distinct col1 from tab1
  2  /
select distinct col1 from tab1
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB


-- union that does distinct sort not allowed on clob column:
SCOTT@orcl_11g> select * from tab1
  2  union
  3  select * from tab2
  4  /
select * from tab1
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB


-- union all is allowed:
SCOTT@orcl_11g> select * from tab1
  2  union all
  3  select * from tab2
  4  /

COL1
--------------------------------------------------------------------------------
test1
test2

SCOTT@orcl_11g> create materialized view test_mview
  2  as
  3  select * from tab1
  4  union all
  5  select * from tab2
  6  /

Materialized view created.

SCOTT@orcl_11g> select * from test_mview
  2  /

COL1
--------------------------------------------------------------------------------
test1
test2

SCOTT@orcl_11g> 

Re: (split from unrelated hijacked thread in wrong forum) [message #395642 is a reply to message #395534] Thu, 02 April 2009 02:37 Go to previous message
m2c_analyst
Messages: 4
Registered: March 2009
Junior Member
Thanks a lot,
My view has been created without any problem now.
Previous Topic: Search Characters automatically in a whole Database
Next Topic: prevent the data from update,insert,delete
Goto Forum:
  


Current Time: Sat Dec 10 04:53:17 CST 2016

Total time taken to generate the page: 0.11485 seconds