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  |
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 #365201 is a reply to message #365121] |
Thu, 11 December 2008 01:28  |
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 #365261 is a reply to message #365213] |
Thu, 11 December 2008 04:29 |
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 |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 15 20:44:15 CST 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
