Home » SQL & PL/SQL » SQL & PL/SQL » Creation of view with clob column in select and group by clause (merged 3)
Creation of view with clob column in select and group by clause (merged 3) [message #365115] Thu, 11 December 2008 00:46 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,

We are trying to migrate a view from sql server2005 to oracle 10g. It has clob column which is used in group by clause. How can the same be achived in oracle 10g.


Below is the sql statament used in creating view aling with its datatypes.


CREATE OR REPLACE FORCE VIEW "MIGRATION"."GUIDES_TEST" 
("CONTENT_ID", "TITLE", "KEYWORDS", "CONTENT", "ISPOPUP", 
"CREATED", "SEARCHSTARTDATE", "SEARCHENDDATE", "HITS", 
"TYPE", "CREATEDBY", "UPDATED", "ISDISPLAYED", "UPDATEDBY", 
"AVERAGERATING", "VOTES") AS 
  SELECT content_ec.content_id,
          content_ec.title,
          content_ec.keywords,
          content_ec.content content ,
          content_ec.ispopup,
          content_ec.created,
          content_ec.searchstartdate,
          content_ec.searchenddate,
        COUNT(contenttracker_ec.contenttracker_id) hits,
          contenttypes_ec.type,
          users_ec_1.username createdby,
          Backup_Latest.created updated,
          Backup_Latest.isdisplayed,
          users_ec_1.username updatedby,
          guideratings.averagerating,
          guideratings.votes 
     FROM users_ec users_ec_1
            JOIN Backup_Latest
             ON users_ec_1.USER_ID = Backup_Latest.USER_ID
            RIGHT JOIN content_ec
            JOIN contenttypes_ec
             ON content_ec.contenttype_id = contenttypes_ec.contenttype_id
             ON Backup_Latest.content_id = content_ec.content_id
            LEFT JOIN guideratings
             ON content_ec.content_id = guideratings.content_id
            LEFT JOIN contenttracker_ec
             ON content_ec.content_id = contenttracker_ec.content_id
            LEFT JOIN users_ec users_ec_2
             ON content_ec.user_id = users_ec_2.USER_ID
     GROUP BY content_ec.content_id,
     content_ec.title,
     content_ec.keywords,
     to_char(content_ec.content) ,
     content_ec.ispopup,
     content_ec.created,
     content_ec.searchstartdate,
     content_ec.searchenddate,
     contenttypes_ec.type,
     users_ec_1.username,
     Backup_Latest.created,
     Backup_Latest.isdisplayed,
     users_ec_1.username,
     guideratings.averagerating,
     guideratings.votes;
 







Column Name      Data TYpe

CONTENT_ID	NUMBER(10,0)
TITLE		VARCHAR2(50)
KEYWORDS	VARCHAR2(100)
CONTENT		CLOB
ISPOPUP		NUMBER(1,0)
CREATED		TIMESTAMP(6)
SEARCHSTARTDATE	TIMESTAMP(6)
SEARCHENDDATE	TIMESTAMP(6)
HITS		NUMBER
TYPE		VARCHAR2(50)
CREATEDBY	VARCHAR2(20)
UPDATED		TIMESTAMP(6)
ISDISPLAYED	NUMBER(1,0)
UPDATEDBY	VARCHAR2(20)
AVERAGERATING	NUMBER
VOTES		NUMBER



Any help realyy appreciated.

Thanks in advance

[Updated on: Thu, 11 December 2008 00:56] by Moderator

Report message to a moderator

Re: Creation of view with clob column in select and group by clause. [message #365121 is a reply to message #365115] Thu, 11 December 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What happened when you tried to execute it?
What is your Oracle version (4 decimals)?
Post your table create statement.
Keep your lines in 80 character width.
Don't multipost.

Regards
Michel

[Updated on: Thu, 11 December 2008 00:56]

Report message to a moderator

Re: Creation of view with clob column in select and group by clause (merged 3) [message #365125 is a reply to message #365115] Thu, 11 December 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
STOP repeating the same question but answer mine.

Regards
Michel

[Updated on: Thu, 11 December 2008 01:05]

Report message to a moderator

Re: Creation of view with clob column in select and group by clause. [message #365201 is a reply to message #365121] Thu, 11 December 2008 01:28 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi Michel,

Here below is the version of oracle I am using.

In the above view column content is having a datatype CLOB. Which is used in group by clause.

In Sqlserver the column content is having a datatype varchar(max). Where it is used in group by clause. It worked fine without any issues.

While migration the oracle equivalent datatype to varchar(max) is clob.

When tried the same in oracle with clob. I am having an issue.

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


Any help really appreciated.

Thanks

[Updated on: Thu, 11 December 2008 02:20] by Moderator

Report message to a moderator

Re: Creation of view with clob column in select and group by clause. [message #365213 is a reply to message #365201] Thu, 11 December 2008 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am having an issue.

I don't see any in what you posted.
I neither see the create statement for the table I requested.

Use code tags when appropriate not for your comment. Lines are not splitted when you use code tags. This why clicking on Preview is important.

Regards
Michel
Re: Creation of view with clob column in select and group by clause (merged 3) [message #365228 is a reply to message #365115] Thu, 11 December 2008 03:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't group by a CLOB.

Your options are either to select the CLOB in a join after you've done the GROUP BY part of the query, or to use CAST to change the CLOB into a varchar2(4000).
Re: Creation of view with clob column in select and group by clause. [message #365261 is a reply to message #365213] Thu, 11 December 2008 04:29 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Below is the create view script.

SQL> CREATE OR REPLACE FORCE VIEW "MIGRATION"."TEST" 
("CONTENT_ID", "TITLE", "KEYWORDS", "CONTENT", "ISPOPUP", 
"CREATED", "SEARCHSTARTDATE", "SEARCHENDDATE", "HITS", 
"TYPE", "CREATEDBY", "UPDATED", "ISDISPLAYED", "UPDATEDBY", 
"AVERAGERATING", "VOTES") AS
  2    SELECT content_ec.content_id,
  3            content_ec.title,
  4            content_ec.keywords,
  5            content_ec.content content ,
  6            content_ec.ispopup,
  7            content_ec.created,
  8            content_ec.searchstartdate,
  9            content_ec.searchenddate,
 10          COUNT(contenttracker_ec.contenttracker_id) hits,
 11            contenttypes_ec.type,
 12            users_ec_1.username createdby,
 13            Backup_Latest.created updated,
 14            Backup_Latest.isdisplayed,
 15            users_ec_1.username updatedby,
 16            guideratings.averagerating,
 17            guideratings.votes
 18       FROM users_ec users_ec_1
 19              JOIN Backup_Latest
 20               ON users_ec_1.USER_ID = Backup_Latest.USER_ID
 21              RIGHT JOIN content_ec
 22              JOIN contenttypes_ec
 23               ON content_ec.contenttype_id = contenttypes_ec.contenttype_id
 24               ON Backup_Latest.content_id = content_ec.content_id
 25              LEFT JOIN guideratings
 26               ON content_ec.content_id = guideratings.content_id
 27              LEFT JOIN contenttracker_ec
 28               ON content_ec.content_id = contenttracker_ec.content_id
 29              LEFT JOIN users_ec users_ec_2
 30               ON content_ec.user_id = users_ec_2.USER_ID
 31       GROUP BY content_ec.content_id,
 32       content_ec.title,
 33       content_ec.keywords,
 34       to_char(content_ec.content) ,
 35       content_ec.ispopup,
 36       content_ec.created,
 37       content_ec.searchstartdate,
 38       content_ec.searchenddate,
 39       contenttypes_ec.type,
 40       users_ec_1.username,
 41       Backup_Latest.created,
 42       Backup_Latest.isdisplayed,
 43       users_ec_1.username,
 44       guideratings.averagerating,
 45       guideratings.votes;

Warning: View created with compilation errors.


SQL> spool off


Thanks in advance

[Updated on: Thu, 11 December 2008 05:07] by Moderator

Report message to a moderator

Re: Creation of view with clob column in select and group by clause (merged 3) [message #365262 is a reply to message #365228] Thu, 11 December 2008 04:31 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Could you please give one small example for above scenario.

It will really help a lot for me.

Thanks in advance.
Re: Creation of view with clob column in select and group by clause (merged 3) [message #365274 is a reply to message #365262] Thu, 11 December 2008 05:09 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again modify your statement to keep your lines in 80 characters.

And I still don't see any statement from tables.
You want help, post what is requested as it is requested.

Regards
Michel
Previous Topic: Sql Query
Next Topic: Regarding Grouping of data
Goto Forum:
  


Current Time: Mon Dec 05 19:12:41 CST 2016

Total time taken to generate the page: 0.07309 seconds