Home » SQL & PL/SQL » SQL & PL/SQL » Crosstab query, Clob columns (10g)
Crosstab query, Clob columns [message #434913] Fri, 11 December 2009 11:10 Go to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
Hi,

Can this be done.

Main table
(COL1, COL2,COL3 & COL4 are CLOB)

ID   COL1   COL2   COL3   COL4
1    A
1           B
1                  C
1                         D


Need the output as following

ID   COL1   COL2   COL3   COL4
1    A      B      C      D



MAX MIN doesn't work on clob so not sure how to this without outer joining the table 4 times.

Suggestions please.

Thanks
AK
Re: Crosstab query, Clob columns [message #434915 is a reply to message #434913] Fri, 11 December 2009 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Crosstab query, Clob columns [message #434916 is a reply to message #434913] Fri, 11 December 2009 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Suggestions please.
What should happen when a single column is NOT NULL in more than 1 row?
icon12.gif  Re: Crosstab query, Clob columns [message #434917 is a reply to message #434913] Fri, 11 December 2009 11:34 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
If you are sure the length of those CLOB's are <= 4000 characters or don't mind geting only the first 4000 charactes, then convert to VARCHAR2:
SELECT Id
     , MAX (Col1) Col1, MAX (Col2) Col2
     , MAX (DBMS_LOB.SUBSTR (Col3, 1, LEAST (DBMS_LOB.LENGTH(Col3), 4000))) Col3
     , MAX (DBMS_LOB.SUBSTR (Col4, 1, LEAST (DBMS_LOB.LENGTH(Col4), 4000))) Col4
  FROM   --etc --
 GROUP BY Id

Razz
Re: Crosstab query, Clob columns [message #434919 is a reply to message #434917] Fri, 11 December 2009 12:01 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
create table ak_id_tab (id number);

insert into ak_id_tab (id) values (1);

create table ak_tab_test (id number, field_id number, clob_col clob);

insert into ak_tab_test (id, field_id, clob_col) values(1,1001,'a');

insert into ak_tab_test (id, field_id, clob_col) values(1,1002,'b');

insert into ak_tab_test (id, field_id, clob_col) values(1,1003,'c');

commit;

select * from ak_tab_test;

ID	FIELD_ID	CLOB_COL

1	1001	        A
1	1002	        B
1	1003	        C



select t0.id, t1.clob_col field_id1, t2.clob_col field_id2, t3.clob_col field_id3
from ak_id_tab t0,  
 (select id, clob_col from ak_tab_test where field_id = 1001) t1,
 (select id,  clob_col from ak_tab_test where field_id = 1002) t2,
 (select id, clob_col from ak_tab_test where field_id = 1002) t3
where
 t0.id = t1.id(+) and
 t0.id = t2.id(+) and
 t0.id = t3.id(+)

 
ID	FIELD_ID1	FIELD_ID2	FIELD_ID3
1       A		B		C
 


Need to do this without self join..
Re: Crosstab query, Clob columns [message #434920 is a reply to message #434917] Fri, 11 December 2009 12:03 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
No Message Body

[Updated on: Fri, 11 December 2009 12:03]

Report message to a moderator

Re: Crosstab query, Clob columns [message #434921 is a reply to message #434917] Fri, 11 December 2009 12:04 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
LKBrwn_DBA wrote on Fri, 11 December 2009 23:04
If you are sure the length of those CLOB's are <= 4000 characters or don't mind geting only the first 4000 charactes, then convert to VARCHAR2:
SELECT Id
     , MAX (Col1) Col1, MAX (Col2) Col2
     , MAX (DBMS_LOB.SUBSTR (Col3, 1, LEAST (DBMS_LOB.LENGTH(Col3), 4000))) Col3
     , MAX (DBMS_LOB.SUBSTR (Col4, 1, LEAST (DBMS_LOB.LENGTH(Col4), 4000))) Col4
  FROM   --etc --
 GROUP BY Id

Razz



Thanks... But its a clob..no truncation at 4000
Re: Crosstab query, Clob columns [message #434922 is a reply to message #434916] Fri, 11 December 2009 12:05 Go to previous message
amitkr
Messages: 17
Registered: November 2007
Junior Member
BlackSwan wrote on Fri, 11 December 2009 23:03
>Suggestions please.
What should happen when a single column is NOT NULL in more than 1 row?


Thanks.. At most only 1 row will be not null.
Previous Topic: subtract dates
Next Topic: Find the Last Character
Goto Forum:
  


Current Time: Sat Dec 03 01:16:58 CST 2016

Total time taken to generate the page: 0.13025 seconds