Re: union in clob

Ok the Oracle cannot do distint on lob datatypes

now when you say UNION Oracle tries to Select the Distinct , but could not do due to Lobs ,

Now try this UNION ALL,
this will not select the Distinct records , instead select all the records, from both queries.

Faheem

gourav_jain_at_yahoo.com (Gourav Jain) wrote in message news:<b659da94.0109272227.74cd7ff3_at_posting.google.com>...
> Hi all,
> I want to run this query in which i want the field msg_text from the
> table mass_mail when the mail_id flag in the mesg_inbox table is set
> else the msg_text is used from msgs_inbox table so i have written this
> query for this problem
>
> CREATE OR REPLACE VIEW allmail AS
> SELECT msgs_inbox.user_id, mass_mail.msg_text, msgs_inbox.mail_id
> FROM msgs_inbox, mass_mail
> where msgs_inbox.mail_id = mass_mail.mail_id
> UNION
> SELECT user_id, msg_text, header, mail_id
> FROM msgs_inbox WHERE mail_id IS NULL
>
> Where msg_text is clob
>
> but when i execute this query it gives the error
>
> ERROR: ORA-00932: inconsistent datatypes
>
> so please help me how can i run this query
> thanks in advance
>
> Gourav Jain
> S/r Software Engg.
> Webdunia.com India (p) Ltd.
Received on Sat Sep 29 2001 - 00:36:05 CEST