Home » SQL & PL/SQL » SQL & PL/SQL » Creating a 'grouping' view
Creating a 'grouping' view [message #230650] Thu, 12 April 2007 08:32 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi there,
I have a view that lists 2 columns and is populated as follows:
Project_Name PDSID

Name1        P0001
Name2        P0003
Name3        P0001
Name4        P0004
Name5        P0003



I need to create a view that groups tge PDSID column and comma separates the names that share the same PDSID:

PDSID   Name
P0001   Name1, Name3
P0003   Name2, Name5
P0004   Name4


can anybody help me acheive this.
I suppose I may need a function - although I have no idea how to write anything like that.

thank sin advance,
Matt



Re: Creating a 'grouping' view [message #230652 is a reply to message #230650] Thu, 12 April 2007 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for stragg or concat_all

Regards
Michel
Re: Creating a 'grouping' view [message #230661 is a reply to message #230650] Thu, 12 April 2007 09:29 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

thanks Michel,

is Concat_all specific to a particular version of Oracle?
I have tried running the scripts I found in the search, but the concat_all function does not get created.

it creates CONCAT_EXP type, but nothing else from the scripts.

thanks,
Matt
Re: Creating a 'grouping' view [message #230665 is a reply to message #230650] Thu, 12 April 2007 09:40 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

more info..
when I run the concat_all scripts I receive this error in SQL Plus:

Warning: Type created with compilation errors.

it does not give me any more info than that.
If I run them in the procedures editor within TOAD, it does not display any error or warning messages...
Re: Creating a 'grouping' view [message #230681 is a reply to message #230665] Thu, 12 April 2007 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know TOAD.
I always used, use, will use SQL*Plus.
Then after the create type use:
show errors

Regards
Michel


Re: Creating a 'grouping' view [message #230684 is a reply to message #230650] Thu, 12 April 2007 10:35 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

this is the error I get when running it with SQL*Plus

SQL> show errors
Errors for FUNCTION CONCAT_ALL:

LINE/COL ERROR
-------- -------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
4/19     PLS-00201: identifier 'CONCAT_ALL_OT' must be declared
SQL> 


any ideas?


this is the code I am running:
--CONCAT_EXPR 

create or replace TYPE "CONCAT_EXPR" AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),
  MAP MEMBER FUNCTION f_map RETURN VARCHAR2);
/


create or replace TYPE BODY concat_expr AS
  MAP MEMBER FUNCTION f_map
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN str;
  END f_map;
END;
/


--concat_all_ot 

create or replace TYPE "concat_all_ot"  AS OBJECT (
  str VARCHAR2 (4000),
  del VARCHAR2 (4000),

  STATIC FUNCTION odciaggregateinitialize (
  sctx IN OUT concat_all_ot)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregateiterate (
  SELF IN OUT concat_all_ot,
  ctx IN concat_expr)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregateterminate (
  SELF IN concat_all_ot,
  returnvalue OUT VARCHAR2,
  flags IN NUMBER)
  RETURN NUMBER,

  MEMBER FUNCTION odciaggregatemerge (
  SELF IN OUT concat_all_ot,
  ctx2 concat_all_ot)
  RETURN NUMBER);
/


create or replace TYPE BODY concat_all_ot
AS
  STATIC FUNCTION odciaggregateinitialize (
  sctx IN OUT concat_all_ot)
  RETURN NUMBER
  IS
  BEGIN
  sctx := concat_all_ot (NULL, NULL);
  RETURN odciconst.success;
  END;
  MEMBER FUNCTION odciaggregateiterate (
  SELF IN OUT concat_all_ot,
  ctx IN concat_expr)
  RETURN NUMBER
  IS
  BEGIN
  IF SELF.str IS NOT NULL THEN
    SELF.str := SELF.str || ctx.del;
  END IF;
  SELF.str := SELF.str || ctx.str;
  RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregateterminate (
  SELF IN concat_all_ot,
  returnvalue OUT VARCHAR2,
  flags IN NUMBER)
  RETURN NUMBER
  IS
  BEGIN
  returnvalue := SELF.str;
  RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregatemerge (
  SELF IN OUT concat_all_ot,
  ctx2 IN concat_all_ot)
  RETURN NUMBER
  IS
  BEGIN
  IF SELF.str IS NOT NULL THEN
    SELF.str := SELF.str || SELF.del;
  END IF;
  SELF.str := SELF.str || ctx2.str;
  RETURN odciconst.success;
  END;
END;
/


create or replace FUNCTION concat_all (
  ctx IN concat_expr)
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  AGGREGATE USING concat_all_ot;
/
Re: Creating a 'grouping' view [message #230686 is a reply to message #230684] Thu, 12 April 2007 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add "show errors" after each statement.

Regards
Michel
Re: Creating a 'grouping' view [message #230689 is a reply to message #230650] Thu, 12 April 2007 10:39 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

tried running the concal_all_ot with its 'type' etc.
and get this message:

SQL> show errors
Errors for TYPE BODY CONCAT_ALL_OT:

LINE/COL ERROR
-------- --------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/11     PLS-00201: identifier 'CONCAT_ALL_OT' must be declared
1/11     PLS-00304: cannot compile body of 'CONCAT_ALL_OT' without its
         specification

SQL> 
Re: Creating a 'grouping' view [message #230701 is a reply to message #230689] Thu, 12 April 2007 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please reexecute the script after putting "show errors" after each statement and "set echo off".
Then post the result.

Regards
Michel
Re: Creating a 'grouping' view [message #230871 is a reply to message #230701] Fri, 13 April 2007 02:42 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi matpj,

its prefect function

 CREATE OR REPLACE FUNCTION concat_all_8i
     (p_columns_to_concat  IN VARCHAR2,
    	p_table_names	     IN VARCHAR2,
    	p_pivot_columns      IN VARCHAR2,
    	p_pivot_values	     IN VARCHAR2,
    	p_delimiter	     IN VARCHAR2 DEFAULT ', ',
    	p_where_clause	     IN VARCHAR2 DEFAULT '1=1',
    	p_order_by	     IN VARCHAR2 DEFAULT NULL)
      RETURN VARCHAR2
    AS
    TYPE ref_cursor_type  IS REF CURSOR;
     v_ref_cursor_variable	ref_cursor_type;
     v_value_to_concat	VARCHAR2 (4000);
     v_concatenated_values	VARCHAR2 (4000);
   BEGIN
     OPEN v_ref_cursor_variable FOR
   	   'SELECT '   || p_columns_to_concat
    || ' FROM '     || p_table_names
     || ' WHERE '    || p_pivot_columns || ' = :b_pivot_values'
     || ' AND ' || p_where_clause
    || ' ORDER BY ' || NVL (p_order_by, p_columns_to_concat)
     USING p_pivot_values;
     LOOP
   	 FETCH v_ref_cursor_variable INTO v_value_to_concat;
   	 EXIT WHEN v_ref_cursor_variable%NOTFOUND;
   	 v_concatenated_values := v_concatenated_values || p_delimiter || v_value_to_concat;
     END LOOP;
    CLOSE v_ref_cursor_variable;
     RETURN LTRIM (v_concatenated_values, p_delimiter);
   END concat_all_8i;]

/

[Updated on: Fri, 13 April 2007 02:44]

Report message to a moderator

Re: Creating a 'grouping' view [message #230873 is a reply to message #230871] Fri, 13 April 2007 02:47 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
just u can verification

SELECT deptno,
concat_all_8i ('ename', 'emp', 'deptno', deptno) AS employees
FROM emp
GROUP BY deptno
ORDER BY deptno[/CODE]

DEPTNO EMPLOYEES

10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
Re: Creating a 'grouping' view [message #230888 is a reply to message #230650] Fri, 13 April 2007 04:00 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

thanks for that.
the function is created correctly.
can you tell me what the arguments are:

my table name is
dsti_new_proj_view

the columns are:
PDSID
PROJECTNAME

I want to group by PDSID and concatonate all of the project names for that ID.


so I tried:
select PDSID,
concat_all_8i (projectname, 'PDSID', PDSID) AS names
FROM niku.dsti_new_proj_view
GROUP BY PDSID
ORDER BY PDSID


but it tells me:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'CONCAT_ALL_8I'



any ideas?

regards,
Matt

Re: Creating a 'grouping' view [message #230898 is a reply to message #230888] Fri, 13 April 2007 04:32 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
u can try it

select PDSID,
concat_all_8i ('projectname','niku.dsti_new_proj_view', 'PDSID', PDSID) AS names
FROM niku.dsti_new_proj_view
GROUP BY PDSID
ORDER BY PDSID

have u any error? let me know.

thanks
srinivas
Re: Creating a 'grouping' view [message #230903 is a reply to message #230650] Fri, 13 April 2007 04:49 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

srinivas,
it works!!

thank you very much for that.
Re: Creating a 'grouping' view [message #234236 is a reply to message #230898] Mon, 30 April 2007 09:50 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

pavuluri wrote on Fri, 13 April 2007 10:32
u can try it

select PDSID,
concat_all_8i ('projectname','niku.dsti_new_proj_view', 'PDSID', PDSID) AS names
FROM niku.dsti_new_proj_view
GROUP BY PDSID
ORDER BY PDSID

have u any error? let me know.

thanks
srinivas


Hi again Srinivas.

I was wondering if you could help me with my code again.
Your SQL:
select PDSID,
concat_all_8i ('projectname','niku.dsti_new_proj_view', 'PDSID', PDSID) AS names
FROM niku.dsti_new_proj_view
GROUP BY PDSID
ORDER BY PDSID

Using the same result, I needed to also return another field.
The view I am using has an additional field in it

I need to use this additional field to order the results returned by your concatonate SQL.

my view contains the additional column CLARITYID

Project_Name PDSID  CLARITYID

Name1        P0001  DST00001
Name2        P0003  DST00002
Name3        P0001  DST00003
Name4        P0004  DST00004
Name5        P0003  DST00005


the output of your SQL needs to list the project names in CLARITY ID order, without actually returning the ID... if that makes sense.

Is this possible?

thanks in advance,
Matt








Re: Creating a 'grouping' view [message #234266 is a reply to message #230665] Mon, 30 April 2007 11:38 Go to previous messageGo to next message
vigneshbemba
Messages: 17
Registered: April 2007
Location: chennai
Junior Member
i got a question its good if anyone can solve... 'is there a table called 'quarter table' please explain if its there..
thank you for your reply!
Re: Creating a 'grouping' view [message #234287 is a reply to message #234266] Mon, 30 April 2007 13:29 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
vigneshbemba wrote on Mon, 30 April 2007 12:38
i got a question its good if anyone can solve... 'is there a table called 'quarter table' please explain if its there..
thank you for your reply!



I don't understand. Where? Unless you create it, it's not there.
Previous Topic: Cursors.
Next Topic: Error ORA-00978
Goto Forum:
  


Current Time: Fri Dec 02 22:41:04 CST 2016

Total time taken to generate the page: 0.12305 seconds