Home » SQL & PL/SQL » SQL & PL/SQL » A question about model clause (merged by CM) (10.2.0.4 on Solaris 8)
A question about model clause (merged by CM) [message #444195] Fri, 19 February 2010 04:50 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #444204 is a reply to message #444195] Fri, 19 February 2010 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/443647/102589/#msg_443647

Regards
Michel
Re: A question about model clause (merged by CM) [message #444207 is a reply to message #444204] Fri, 19 February 2010 06:02 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
We are on oracle 10.2.0.4, and this function is not working. Is it an oracle 11g feature?
Re: A question about model clause (merged by CM) [message #444208 is a reply to message #444207] Fri, 19 February 2010 06:04 Go to previous messageGo to next message
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 Smile

[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 Go to previous messageGo to next message
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

Re: A question about model clause (merged by CM) [message #444212 is a reply to message #444209] Fri, 19 February 2010 06:28 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Sql+>select  wm_concat(distinct value) from rt;

WM_CONCAT(DISTINCTVALUE)
-------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value5

Sql+>select id,value from rt;

        ID VALUE
---------- ------
         1 Value1
         2 Value2
         3 Value3
         4 Value4
         5 Value4
         6
         7 Value5
         8

8 rows selected.


http://www.sqlsnippets.com/en/topic-12092.html
Goodluck
sriram Smile
Re: A question about model clause (merged by CM) [message #444213 is a reply to message #444212] Fri, 19 February 2010 06:36 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thanks a lot for your help. I think the wm_concat function is undocumented as of Oracle 10g, and I am still on 10g, so I cant use it correct?

so can someone help for model clause - how to get the data using it?

Re: A question about model clause (merged by CM) [message #444215 is a reply to message #444213] Fri, 19 February 2010 06:55 Go to previous message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Did you read the thread what Michel posted?
It has so many links...
for that type of functions(user defined too).
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:250238800346872505
Asktom

For model clause...
See my previos message(link) which will include somany examples...

sriram Smile

Previous Topic: ORA-01801: date format is too long for internal buffer
Next Topic: Java-script and Oracle DB updating a table item
Goto Forum:
  


Current Time: Sun Nov 10 05:24:31 CST 2024