A question about model clause (merged by CM) [message #444195] |
Fri, 19 February 2010 04:50 |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
Hi,
I am trying to use model clause to get comma seperate single row for multiple rows. My scenario is like this:
SQL> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
VALUE CHAR(6)
SQL> select * from test1 order by id;
ID VALUE
---------- ------
1 Value1
2 Value2
3 Value3
4 Value4
5 Value4
6
7 value5
8
The query that I have is:
SQL> with t as
2 ( select distinct substr(value,2) value
3 from test1
4 model
5 ignore nav
6 dimension by (id)
7 measures (cast(value as varchar2(100)) value)
8 rules
9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()]
10 )
11 )
12 select max(value) oneline
13 from t;
ONELINE
---------------------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value4,,value5,
what I want is : null value should not come and duplicate value should not come (Value4 in output above)
can someone help on this?
thanks
CM: fixed code tags - they should only go around code.
[Updated on: Fri, 19 February 2010 05:07] by Moderator Report message to a moderator
|
|
|
Re: A question about model clause (merged by CM) [message #444199 is a reply to message #444195] |
Fri, 19 February 2010 05:16 |
narsap
Messages: 8 Registered: December 2009
|
Junior Member |
|
|
Won't that be just
with src as (
select distinct value
from test1
where value is not null
)
with t as (
select distinct substr(value,2) value
from src
model
ignore nav
dimension by (id)
measures (cast(value as varchar2(100)) value)
rules
(
value[any] order by id =
value[cv()-1] || ',' || value[cv()]
)
)
select max(value) oneline
from t;
** Not Tested
[Updated on: Fri, 19 February 2010 05:16] Report message to a moderator
|
|
|
Re: A question about model clause (merged by CM) [message #444200 is a reply to message #444199] |
Fri, 19 February 2010 05:26 |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
thank you, I tried your suggestion , it is giving me following error:
SQL> with src as (
2 select value
3 from test1
4 where value is not null
5 )
6 with t as (
7 select distinct substr(value,2) value
8 from src
9 model
10 ignore nav
11 dimension by (id)
12 measures (cast(value as varchar2(100)) value)
13 rules
14 (
15 value[any] order by id =
16 value[cv()-1] || ',' || value[cv()]
17 )
18 )
19 select max(value) oneline
20 from t;
with t as (
*
[b]ERROR at line 6:
ORA-00928: missing SELECT keyword[/b]
|
|
|
Re: A question about model clause (merged by CM) [message #444201 is a reply to message #444195] |
Fri, 19 February 2010 05:30 |
cookiemonster
Messages: 13952 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Corrected with syntax:
with src as (
select distinct value
from test1
where value is not null
),
t as (
select distinct substr(value,2) value
from src
model
ignore nav
dimension by (id)
measures (cast(value as varchar2(100)) value)
rules
(
value[any] order by id =
value[cv()-1] || ',' || value[cv()]
)
)
select max(value) oneline
from t;
No idea if that actually fixes your problem though.
|
|
|
Re: A question about model clause (merged by CM) [message #444202 is a reply to message #444201] |
Fri, 19 February 2010 05:36 |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
The query gives following error:
with src as (
select distinct value
from test1
where value is not null
),
t as (
select distinct substr(value,2) value
from src
model
ignore nav
dimension by (id)
measures (cast(value as varchar2(100)) value)
rules
(
value[any] order by id =
value[cv()-1] || ',' || value[cv()]
)
)
select max(value) oneline
from t;
dimension by (id)
*
ERROR at line 11:
ORA-00904: "ID": invalid identifier
that's because the src does not have id column - but when I modify it as below it is giving wrong result
with src as (
select distinct id,value
from test1
where value is not null
),
t as (
select distinct substr(value,2) value
from src
model
ignore nav
dimension by (id)
measures (cast(value as varchar2(100)) value)
rules
(
value[any] order by id =
value[cv()-1] || ',' || value[cv()]
)
)
select max(value) oneline
from t
/
ONELINE
-----------------------------------------------------------------------------------------------
value5 <--incorrect result!
Also is it possible to write the query in such a way that it avoids altogether the "with clause" ? -the reason is , this query is going to be a part of another bigger query which is to be used for implementing pagination.
[Updated on: Fri, 19 February 2010 05:37] Report message to a moderator
|
|
|
|
|
Re: A question about model clause (merged by CM) [message #444208 is a reply to message #444207] |
Fri, 19 February 2010 06:04 |
|
ramoradba
Messages: 2457 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
instead of saying show us what you tried
(How you defined it as "NOT WORKING" like below )
Sql+>select banner from v$version;
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 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Sql+>with
2 data as (
3 select deptno, ename,
4 row_number() over (partition by deptno order by ename) rn,
5 count(*) over (partition by deptno) cnt
6 from raghav.sriram
7 )
8 select deptno,
9 substr(sys_connect_by_path(ename,','),2) emp
10 from data
11 where rn = cnt
12 connect by prior deptno = deptno and prior rn = rn-1
13 start with rn = 1
14 order by deptno
15 /
DEPTNO
----------
EMP
--------------------------------------------------------------------------------
10
CLARK,KING,MILLER,SRIRAM
20
ADAMS,FORD,JONES,SCOTT,SMITH
30
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
Sql+>
sriram
[Updated on: Fri, 19 February 2010 06:08] Report message to a moderator
|
|
|
Re: A question about model clause (merged by CM) [message #444209 is a reply to message #444208] |
Fri, 19 February 2010 06:10 |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
sql>select id,wm_concat(value) from test1
2 group by id;
select id,wm_concat(value) from test1
*
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier
also from a different thread in OTN, where someone has done a lot of anaysis I came to learn that MODEL clause is the most efficient one to meet with this requirement, so I am wish to use the model clause for this purpose. Another thing is, this query is going to be a (small) part of a bigger query which we are going to be used to implement pagination! so I wish not to use the "with clause" , is it possible to use the model clause and yet avoid the with clause?
thanks,
[Updated on: Fri, 19 February 2010 06:13] Report message to a moderator
|
|
|
|
|
|