Home » SQL & PL/SQL » SQL & PL/SQL » Select the Multi Rows of Column in single Row. (Oracle 9i, Forms and Reports 6i)
Select the Multi Rows of Column in single Row. [message #344954] Tue, 02 September 2008 00:21 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hai,

Good Morning


I need to select the Multiple Rows in a Column in Single Row.

Ex.

Select Column1 from Table;

Column1

Patna
Mumbai
Delhi

I needed this data in single row like 

Patna Mumbai Delhi

If it Passable Please reply me.
Re: Select the Multi Rows of Column in single Row. [message #344958 is a reply to message #344954] Tue, 02 September 2008 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "pivot".

Regards
Michel
Re: Select the Multi Rows of Column in single Row. [message #344972 is a reply to message #344954] Tue, 02 September 2008 01:10 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member

CREATE TABLE table1(column1 VARCHAR2(30))


INSERT INTO table1 values('Patna')
INSERT INTO table1 values('Mumbai')
INSERT INTO table1 values('Delhi')


SELECT 
 MIN(DECODE(ROWNUM,1,column1)) Column1
,MIN(DECODE(ROWNUM,2,column1)) Column2
,MIN(DECODE(ROWNUM,3,column1)) Column3
FROM 
table1
WHERE
column1 IS NOT NULL

Re: Select the Multi Rows of Column in single Row. [message #344985 is a reply to message #344972] Tue, 02 September 2008 02:01 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

similar requirement had been raised through my message #344768. wherein Michel had suggested to use pivot /unpivot. I also tried. when i tried through unpivot multiple columns had come through single column using concatination. The preent case you have answered is good enough when we can know no of rows are standard. For example when it is 3, it works fine. If i add one more row my select statement will get failed to return 4th row. Using procedure to determine the no.of rows and then concatinating the single column values (multiple) works fine.

pivot converted rows to columns
unpivot converted multiple columns to rows.

but my requirment without knowing the definite no.of rows can we be able concatinate the muliple values of single column. On search i have found concate_all concat_string, stragg will help to achieve this result. But i am unable to locate the examples/ codes through google and many other sites.

Michel suggested me to post the test case. Since i have some constraint on posting the test case I was unable to proceed on that thread since i work in different node and browse orafaq in different node.

any help offered in this aspect would be highly thankful and appreciated.

yours
dr.s.raghunathan
Re: Select the Multi Rows of Column in single Row. [message #344991 is a reply to message #344954] Tue, 02 September 2008 02:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or search for CONCAT_ALL or STRAGG - thee are two common string concatenation routines that might fit your requirement.
Re: Select the Multi Rows of Column in single Row. [message #344993 is a reply to message #344985] Tue, 02 September 2008 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But i am unable to locate the examples/ codes through google and many other sites.

You didn't search very well.
If you type "pivot" in search field, in the 3rd result "SQL Query", you will find a hint I gave, if you follow it then you will find many examples i posted on AskTom.

Regards
Michel
Re: Select the Multi Rows of Column in single Row. [message #345002 is a reply to message #344954] Tue, 02 September 2008 02:23 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Read - http://www.orafaq.com/forum/t/44029/0/


SELECT
STRAGG(column1) 
FROM
table1



Re: Select the Multi Rows of Column in single Row. [message #345043 is a reply to message #344991] Tue, 02 September 2008 03:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
spmano1983 wrote on Tue, 02 September 2008 09:23

SELECT
STRAGG(column1) 
FROM
table1



Ooo... You found this all by yourself?
Ah, wait no...
JRowbottom wrote on Tue, 02 September 2008 09:15
Or search for CONCAT_ALL or STRAGG - thee are two common string concatenation routines that might fit your requirement.

Re: Select the Multi Rows of Column in single Row. [message #345067 is a reply to message #345043] Tue, 02 September 2008 04:19 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
I am working oracle XE apex 3.0

I tried the given solution with respect to stragg and concat_all and met with failure. Now i am trying to post the test case
CREATE TABLE  "TABLE1" 
   (	"TABLE1_ID" NUMBER(10,0), 
	"TABLE1_DESC" VARCHAR2(4000), 
	 CONSTRAINT "TABLE1_CON" PRIMARY KEY ("TABLE1_ID") ENABLE
   )
/

insert into table1 values (1001,'1001->');
insert into table1 values (1002,'1002->');
insert into table1 values (1003,'1003->');


CREATE TABLE  "TABLE2" 
   (	"TABLE2_ID" NUMBER(10,0), 
	"TABLE2_VOUNO" NUMBER(10,0)
   )
/
ALTER TABLE  "TABLE2" ADD CONSTRAINT "TABLE2_FK" FOREIGN KEY ("TABLE2_ID")
	  REFERENCES  "TABLE1" ("TABLE1_ID") ENABLE
/


insert into table2 values (1002,12012);
insert into table2 values (1002,12022);
insert into table2 values (1001,12011);
insert into table2 values (1003,12033);
insert into table2 values (1002,12032);
insert into table2 values (1002,12042);
insert into table2 values (1002,12052);
insert into table2 values (1001,12021);
insert into table2 values (1001,12031);
insert into table2 values (1003,12023);


I want the report as 

table2_id    table2_vounos
1001		12011/12021/12031
1002		12012/12022/12032/12042
1003		12033/12023


I tried

select table_id, stragg(table2_vouno) as vou_nos from table2 group by table2_id

ORA-00904: "STRAGG": invalid identifier 

DBMS Output 
  
select table_id, concat_all(concat_expr(table2_vouno,'/')) as vou_nos from table2 group by table2_id

ORA-00904: "CONCAT_ALL": invalid identifier 

DBMS Output 
  


I want to achieve the desired (about output) through select statement. please help me
Re: Select the Multi Rows of Column in single Row. [message #345071 is a reply to message #345067] Tue, 02 September 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
stragg and concat_all are NOT standard functions, you have to search their code here for concat_all and on AskTom for stragg.

Regards
Michel
Re: Select the Multi Rows of Column in single Row. [message #345288 is a reply to message #345067] Tue, 02 September 2008 15:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I have demonstrated various methods below with different requirements and limitations. One requires 11g and is unsupported, some require user-defined functions, and some allow ordering. The generic string aggregation methods are simpler, but you may get better performance from something specific to the situation. Which you choose depends on weighing performance versus code maintenance. If you find minimal difference, then it is easier to use a generic method. I have only used your table2, since table1 is not needed.

-- table and data that you provided:
SCOTT@orcl_11g> CREATE TABLE  "TABLE2"
  2  	(    "TABLE2_ID"     NUMBER(10,0),
  3  	     "TABLE2_VOUNO"  NUMBER(10,0)
  4  	)
  5  /

Table created.

SCOTT@orcl_11g> begin
  2    insert into table2 values (1002,12012);
  3    insert into table2 values (1002,12022);
  4    insert into table2 values (1001,12011);
  5    insert into table2 values (1003,12033);
  6    insert into table2 values (1002,12032);
  7    insert into table2 values (1002,12042);
  8    insert into table2 values (1002,12052);
  9    insert into table2 values (1001,12021);
 10    insert into table2 values (1001,12031);
 11    insert into table2 values (1003,12023);
 12  end;
 13  /

PL/SQL procedure successfully completed.


-- 11g method using undocumented, therefore unsupported wm_concat function:
SCOTT@orcl_11g> COLUMN	table2_vounos FORMAT A45
SCOTT@orcl_11g> SELECT table2_id, wm_concat (table2_vouno) AS table2_vounos
  2  FROM   table2
  3  GROUP  BY table2_id
  4  ORDER  BY table2_id
  5  /

 TABLE2_ID TABLE2_VOUNOS
---------- ---------------------------------------------
      1001 12011,12021,12031
      1002 12012,12022,12032,12052,12042
      1003 12033,12023


-- without any user-defined function and
-- allows ordering by column selected or other column or rowid:
SCOTT@orcl_11g> COLUMN	table2_vounos FORMAT A45
SCOTT@orcl_11g> SELECT	table2_id,
  2  	     MAX (SUBSTR (SYS_CONNECT_BY_PATH (table2_vouno, '/'), 2)) AS table2_vounos
  3  FROM    (SELECT table2_id, table2_vouno,
  4  		     ROW_NUMBER () OVER (PARTITION BY table2_id ORDER BY ROWID) AS rn
  5  	      FROM   table2)
  6  GROUP   BY table2_id
  7  START   WITH rn = 1
  8  CONNECT BY PRIOR table2_id = table2_id AND PRIOR rn = rn - 1
  9  ORDER   BY table2_id
 10  /

 TABLE2_ID TABLE2_VOUNOS
---------- ---------------------------------------------
      1001 12011/12021/12031
      1002 12012/12022/12032/12042/12052
      1003 12033/12023


-- user-defined function specific to the situation that
-- allows ordering and is sometimes more efficient than generic methods:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION vounos
  2    (p_table2_id IN table2.table2_id%TYPE,
  3  	p_delim     IN VARCHAR2)
  4    RETURN VARCHAR2
  5  AS
  6    v_result VARCHAR2 (32767);
  7  BEGIN
  8    FOR r IN
  9  	 (SELECT table2_vouno
 10  	  FROM	 table2
 11  	  WHERE  table2_id = p_table2_id
 12  	  ORDER  BY ROWID)
 13    LOOP
 14  	 v_result := v_result || p_delim || r.table2_vouno;
 15    END LOOP;
 16    RETURN LTRIM (v_result, p_delim);
 17  END vounos;
 18  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT table2_id, vounos (table2_id, '/') AS table2_vounos
  2  FROM   table2
  3  GROUP  BY table2_id
  4  ORDER  BY table2_id
  5  /

 TABLE2_ID TABLE2_VOUNOS
---------- ---------------------------------------------
      1001 12011/12021/12031
      1002 12012/12022/12032/12042/12052
      1003 12033/12023


-- using Tom Kyte's stragg funciton:
-- code from Tom Kyte with delimiter modified:
SCOTT@orcl_11g> create or replace type string_agg_type as object
  2  (
  3  	total varchar2(4000),
  4  
  5  	static function
  6  	     ODCIAggregateInitialize(sctx IN OUT string_agg_type )
  7  	     return number,
  8  
  9  	member function
 10  	     ODCIAggregateIterate(self IN OUT string_agg_type ,
 11  				  value IN varchar2 )
 12  	     return number,
 13  
 14  	member function
 15  	     ODCIAggregateTerminate(self IN string_agg_type,
 16  				    returnValue OUT  varchar2,
 17  				    flags IN number)
 18  	     return number,
 19  
 20  	member function
 21  	     ODCIAggregateMerge(self IN OUT string_agg_type,
 22  				ctx2 IN string_agg_type)
 23  	     return number
 24  );
 25  /

Type created.

SCOTT@orcl_11g> create or replace type body string_agg_type
  2  is
  3  
  4  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
  5  return number
  6  is
  7  begin
  8  	 sctx := string_agg_type( null );
  9  	 return ODCIConst.Success;
 10  end;
 11  
 12  member function ODCIAggregateIterate(self IN OUT string_agg_type,
 13  					  value IN varchar2 )
 14  return number
 15  is
 16  begin
 17  	 self.total := self.total
 18  	 || '/'
 19  	 || value;
 20  	 return ODCIConst.Success;
 21  end;
 22  
 23  member function ODCIAggregateTerminate(self IN string_agg_type,
 24  					    returnValue OUT varchar2,
 25  					    flags IN number)
 26  return number
 27  is
 28  begin
 29  	 returnValue := ltrim(self.total,'/');
 30  	 return ODCIConst.Success;
 31  end;
 32  
 33  member function ODCIAggregateMerge(self IN OUT string_agg_type,
 34  					ctx2 IN string_agg_type)
 35  return number
 36  is
 37  begin
 38  	 self.total := self.total || ctx2.total;
 39  	 return ODCIConst.Success;
 40  end;
 41  
 42  
 43  end;
 44  /

Type body created.

SCOTT@orcl_11g> CREATE or replace
  2  FUNCTION stragg(input varchar2 )
  3  RETURN varchar2
  4  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
  5  /

Function created.

SCOTT@orcl_11g> -- end of code from Tom Kyte
SCOTT@orcl_11g> -- select statement:
SCOTT@orcl_11g> COLUMN	table2_vounos FORMAT A45
SCOTT@orcl_11g> SELECT	table2_id,
  2  	     stragg (table2_vouno) AS table2_vounos
  3  FROM    table2
  4  GROUP   BY table2_id
  5  /

 TABLE2_ID TABLE2_VOUNOS
---------- ---------------------------------------------
      1001 12011/12021/12031
      1002 12012/12022/12032/12052/12042
      1003 12033/12023


-- using concat_all (a modification of Tom Kyte's stragg by James Padfield to allow choice of delimiter):
SCOTT@orcl_11g> CREATE OR REPLACE TYPE concat_expr AS OBJECT (
  2    str VARCHAR2 (4000),
  3    del VARCHAR2 (4000));
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE concat_all_ot AS OBJECT (
  2    str VARCHAR2 (4000),
  3    del VARCHAR2 (4000),
  4  
  5    STATIC FUNCTION odciaggregateinitialize (
  6  	 sctx IN OUT concat_all_ot)
  7  	 RETURN NUMBER,
  8  
  9    MEMBER FUNCTION odciaggregateiterate (
 10  	 SELF IN OUT concat_all_ot,
 11  	 ctx IN concat_expr)
 12  	 RETURN NUMBER,
 13  
 14    MEMBER FUNCTION odciaggregateterminate (
 15  	 SELF IN concat_all_ot,
 16  	 returnvalue OUT VARCHAR2,
 17  	 flags IN NUMBER)
 18  	 RETURN NUMBER,
 19  
 20    MEMBER FUNCTION odciaggregatemerge (
 21  	 SELF IN OUT concat_all_ot,
 22  	 ctx2 concat_all_ot)
 23  	 RETURN NUMBER);
 24  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY concat_all_ot
  2  AS
  3    STATIC FUNCTION odciaggregateinitialize (
  4  	 sctx IN OUT concat_all_ot)
  5  	 RETURN NUMBER
  6    IS
  7    BEGIN
  8  	 sctx := concat_all_ot (NULL, NULL);
  9  	 RETURN odciconst.success;
 10    END;
 11    MEMBER FUNCTION odciaggregateiterate (
 12  	 SELF IN OUT concat_all_ot,
 13  	 ctx IN concat_expr)
 14  	 RETURN NUMBER
 15    IS
 16    BEGIN
 17  	 IF SELF.str IS NOT NULL THEN
 18  	   SELF.str := SELF.str || ctx.del;
 19  	 END IF;
 20  	 SELF.str := SELF.str || ctx.str;
 21  	 RETURN odciconst.success;
 22    END;
 23  
 24    MEMBER FUNCTION odciaggregateterminate (
 25  	 SELF IN concat_all_ot,
 26  	 returnvalue OUT VARCHAR2,
 27  	 flags IN NUMBER)
 28  	 RETURN NUMBER
 29    IS
 30    BEGIN
 31  	 returnvalue := SELF.str;
 32  	 RETURN odciconst.success;
 33    END;
 34  
 35    MEMBER FUNCTION odciaggregatemerge (
 36  	 SELF IN OUT concat_all_ot,
 37  	 ctx2 IN concat_all_ot)
 38  	 RETURN NUMBER
 39    IS
 40    BEGIN
 41  	 IF SELF.str IS NOT NULL THEN
 42  	   SELF.str := SELF.str || SELF.del;
 43  	 END IF;
 44  	 SELF.str := SELF.str || ctx2.str;
 45  	 RETURN odciconst.success;
 46    END;
 47  END;
 48  /

Type body created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION concat_all (
  2    ctx IN concat_expr)
  3    RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  4    AGGREGATE USING concat_all_ot;
  5  /

Function created.

SCOTT@orcl_11g> -- select statement:
SCOTT@orcl_11g> COLUMN	table2_vounos FORMAT A45
SCOTT@orcl_11g> SELECT	table2_id,
  2  	     concat_all (concat_expr (table2_vouno, '/') )AS table2_vounos
  3  FROM    table2
  4  GROUP   BY table2_id
  5  /

 TABLE2_ID TABLE2_VOUNOS
---------- ---------------------------------------------
      1001 12011/12021/12031
      1002 12012/12022/12032/12052/12042
      1003 12033/12023

SCOTT@orcl_11g> 



Re: Select the Multi Rows of Column in single Row. [message #345546 is a reply to message #345288] Thu, 04 September 2008 02:21 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear barbara,

excellent solution you have offered. I created vounos function and using the function i have created view and from the view i have updated table1. I tried to directly update table1 using the following statement.
Quote:


select bb, aa from (select vounos(a.table2_id, ' / ') aa, table2_id bb from table2 a, table1 b where a.table2_id = b.table1_id
group by a.table2_id order by a.table2_id)




on update statement various errors are coming like multi rows returned and some syntax.

Two things i like to know

if i am successful on direct update statement can i insert as trigger after insert. vou_nos are not getting changed on update hence i do not need trigger after update. Though my present application does not require after delete i am curious to know on acadamic interest if i write after delete will the table2_desc also will be written with the removal of table2 vou_nos. of course I am very greedy to refine my requirement. However, I am very jubiliant and once again thanking you for the effort you have made.

yours
dr.s.raghunathan

In good old days 1983 when dbase II operation, i do not have any manual coining the commands were made under test and trial. It has taken enormous time sometimes even a month. When i become faculty for such subjects, students learnt the things just like that. Likewise, I am enjoying the experts opinion.
Re: Select the Multi Rows of Column in single Row. [message #345736 is a reply to message #345546] Thu, 04 September 2008 12:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Duplicate data storage is generally a bad idea. It is better to only store data in normalized form. There is no need to store a concatenated string of values in your table1_desc column when you can just select it when you want to display it that way. Using a view to do this is fine. However, if for some strange reason you insist on updating this, then I have demonstrated how you can do this with a simple update statement below. You couldn't figure out the simple update statement below?

UPDATE table1 SET table1_desc = vounos (table1_id, '/');

You could certainly write an after insert row trigger on table2 to concatenate the inserted value onto table1_desc and you could create an after delete row trigger on table2 to replace that deleted value with null within the concatenated string in table1_desc. However, once again, this is a bad way to store data. Not only is it unnecessary duplicate storage, but it is not normalized. If you want to display the data this way, then create a view on the select statement that uses the function, and select from the view. Forget about the update statement and/or triggers that will clutter your database and slow down your input.

SCOTT@orcl_11g> -- tables, constraints, and data that you provided:
SCOTT@orcl_11g> CREATE TABLE  "TABLE1"
  2  	(    "TABLE1_ID" NUMBER(10,0),
  3  	     "TABLE1_DESC" VARCHAR2(4000),
  4  	      CONSTRAINT "TABLE1_CON" PRIMARY KEY ("TABLE1_ID") ENABLE
  5  	)
  6  /

Table created.

SCOTT@orcl_11g> begin
  2    insert into table1 values (1001,'1001->');
  3    insert into table1 values (1002,'1002->');
  4    insert into table1 values (1003,'1003->');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE TABLE  "TABLE2"
  2  	(    "TABLE2_ID"     NUMBER(10,0),
  3  	     "TABLE2_VOUNO"  NUMBER(10,0)
  4  	)
  5  /

Table created.

SCOTT@orcl_11g> ALTER TABLE  "TABLE2" ADD CONSTRAINT "TABLE2_FK" FOREIGN KEY ("TABLE2_ID")
  2  	       REFERENCES  "TABLE1" ("TABLE1_ID") ENABLE
  3  /

Table altered.

SCOTT@orcl_11g> begin
  2    insert into table2 values (1002,12012);
  3    insert into table2 values (1002,12022);
  4    insert into table2 values (1001,12011);
  5    insert into table2 values (1003,12033);
  6    insert into table2 values (1002,12032);
  7    insert into table2 values (1002,12042);
  8    insert into table2 values (1002,12052);
  9    insert into table2 values (1001,12021);
 10    insert into table2 values (1001,12031);
 11    insert into table2 values (1003,12023);
 12  end;
 13  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- function you decided to use:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION vounos
  2    (p_table2_id IN table2.table2_id%TYPE,
  3  	p_delim     IN VARCHAR2)
  4    RETURN VARCHAR2
  5  AS
  6    v_result VARCHAR2 (32767);
  7  BEGIN
  8    FOR r IN
  9  	 (SELECT table2_vouno
 10  	  FROM	 table2
 11  	  WHERE  table2_id = p_table2_id
 12  	  ORDER  BY ROWID)
 13    LOOP
 14  	 v_result := v_result || p_delim || r.table2_vouno;
 15    END LOOP;
 16    RETURN LTRIM (v_result, p_delim);
 17  END vounos;
 18  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- select statement using above function to get aggregated string of vounos
SCOTT@orcl_11g> -- (you could create a view from this statment in a view if you like):
SCOTT@orcl_11g> COLUMN table2_vounos FORMAT A45
SCOTT@orcl_11g> SELECT table2_id, vounos (table2_id, '/') AS table2_vounos
  2  FROM   table2
  3  GROUP  BY table2_id
  4  ORDER  BY table2_id
  5  /

 TABLE2_ID TABLE2_VOUNOS
---------- ---------------------------------------------
      1001 12011/12021/12031
      1002 12012/12022/12032/12042/12052
      1003 12033/12023

SCOTT@orcl_11g> -- update statement:
SCOTT@orcl_11g> UPDATE table1
  2  SET    table1_desc = vounos (table1_id, '/')
  3  /

3 rows updated.

SCOTT@orcl_11g> -- results:
SCOTT@orcl_11g> COLUMN table1_desc FORMAT A45
SCOTT@orcl_11g> SELECT * FROM table1
  2  /

 TABLE1_ID TABLE1_DESC
---------- ---------------------------------------------
      1001 12011/12021/12031
      1002 12012/12022/12032/12042/12052
      1003 12033/12023

SCOTT@orcl_11g>

Re: Select the Multi Rows of Column in single Row. [message #345809 is a reply to message #345736] Thu, 04 September 2008 21:37 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear barbara,

Quote:


Duplicate data storage is generally a bad idea. It is better to only store data in normalized form.




My master table table1 and it has so many child tables and one of the table is table2. Table2 is a very voluminous. While storing data in table2 people do not mind the time factor taken due to triggers whereas when we print data (invoice) from table1 slight delay time kills the users. Hence to collate the vounos in invoice i have created one column at Table1. Normalisation and denormalisation and again normalisation made me to create duplicate data in different form. However as you have suggested, i will try to normalize design once again if it warrants. I also need one more suggestion. I have developed one hotel administration pacakage using oracle 10G Apex 3.0 (which consists of 55 pages) Though not professional approach, it is able to meet the need of users. I shall send DMP,SQl Files. Is there anybody can critically review and suggest the means of improvements. (trusted people /sites) Ofcourse I should have posted second requirement under general new topic. Developed package is not for commercial purpose and developed for learning sake since i am a goverment servant and can not engage any other activities.

Extra information offered in this topic can be ignored.
yours
dr.s.raghunathan
Re: Select the Multi Rows of Column in single Row. [message #345811 is a reply to message #345736] Thu, 04 September 2008 21:49 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

Quote:


If you want to display the data this way, then create a view on the select statement that uses the function, and select from the view. Forget about the update statement and/or triggers that will clutter your database and slow down your input.




Thank you i normalised using the view and removed the column in table1. In table2 some of the data transfered to table name settled (table2 structure) and removed the data in table2 which has been transfered. Hence I was forced to create view based on table2 and settled using the union. I was told union will make enormous delay process (may be i misunderstood). please advise me the approach to select whether to create a view with union or to create duplicated data in different form in the master file itself. Which can be a better solution.

yours
dr.s.raghunathan
Re: Select the Multi Rows of Column in single Row. [message #345825 is a reply to message #345811] Thu, 04 September 2008 23:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
If you eliminate everything you don't need, such as extra joins and group by and order by from the function and the select statement from which the view is created and the select statement that selects from the view, and you maintain current statistics, then you should be able to use an index range scan, as shown below. If this does not help, then please post a copy and paste of a run of function compilation, view creation, and select statement with explain plan as I have done below.

SCOTT@orcl_11g> CREATE TABLE  "TABLE1"
  2  	(    "TABLE1_ID" NUMBER(10,0),
  3  	      CONSTRAINT "TABLE1_CON" PRIMARY KEY ("TABLE1_ID") ENABLE
  4  	)
  5  /

Table created.

SCOTT@orcl_11g> begin
  2    insert into table1 values (1001);
  3    insert into table1 values (1002);
  4    insert into table1 values (1003);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> INSERT INTO table1 SELECT DISTINCT object_id FROM all_objects
  2  WHERE  object_id NOT IN (1001, 1002, 1003)
  3  /

68702 rows created.

SCOTT@orcl_11g> CREATE TABLE  "TABLE2"
  2  	(    "TABLE2_ID"     NUMBER(10,0),
  3  	     "TABLE2_VOUNO"  NUMBER(10,0)
  4  	)
  5  /

Table created.

SCOTT@orcl_11g> ALTER TABLE  "TABLE2" ADD CONSTRAINT "TABLE2_FK" FOREIGN KEY ("TABLE2_ID")
  2  	       REFERENCES  "TABLE1" ("TABLE1_ID") ENABLE
  3  /

Table altered.

SCOTT@orcl_11g> begin
  2    insert into table2 values (1002,12012);
  3    insert into table2 values (1002,12022);
  4    insert into table2 values (1001,12011);
  5    insert into table2 values (1003,12033);
  6    insert into table2 values (1002,12032);
  7    insert into table2 values (1002,12042);
  8    insert into table2 values (1002,12052);
  9    insert into table2 values (1001,12021);
 10    insert into table2 values (1001,12031);
 11    insert into table2 values (1003,12023);
 12  end;
 13  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> INSERT INTO table2 SELECT object_id, namespace FROM all_objects
  2  WHERE  object_id IN (SELECT table1_id FROM table1)
  3  /

68683 rows created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION vounos
  2    (p_table2_id IN table2.table2_id%TYPE,
  3  	p_delim     IN VARCHAR2)
  4    RETURN VARCHAR2
  5  AS
  6    v_result VARCHAR2 (32767);
  7  BEGIN
  8    FOR r IN
  9  	 (SELECT table2_vouno
 10  	  FROM	 table2
 11  	  WHERE  table2_id = p_table2_id)
 12    LOOP
 13  	 v_result := v_result || p_delim || r.table2_vouno;
 14    END LOOP;
 15    RETURN LTRIM (v_result, p_delim);
 16  END vounos;
 17  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TABLE1')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TABLE2')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE OR REPLACE VIEW vounos_view AS
  2  SELECT table1.table1_id, vounos (table1.table1_id, '/') AS table2_vounos
  3  FROM   table1
  4  /

View created.

SCOTT@orcl_11g> COLUMN table2_vounos FORMAT A45
SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SELECT * FROM vounos_view WHERE table1_id BETWEEN 1001 AND 1003
  2  /

 TABLE1_ID TABLE2_VOUNOS
---------- ---------------------------------------------
      1001 12011/12021/12031/4
      1002 12012/12022/12032/12042/12052/1
      1003 12033/12023/4


Execution Plan
----------------------------------------------------------
Plan hash value: 4026648749

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     3 |    12 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TABLE1_CON |     3 |    12 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TABLE1"."TABLE1_ID">=1001 AND "TABLE1"."TABLE1_ID"<=1003)

SCOTT@orcl_11g>


Re: Select the Multi Rows of Column in single Row. [message #345888 is a reply to message #345825] Fri, 05 September 2008 03:54 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear barbara,

Quote:

object_id FROM all_objects
namespace FROM all_objects






I am not familar with the above. Let me understand about these things and will come back to you after test & trial since explain plan, index range scan are also above my head. May be it will take some time to gain clarity on the above wordings.

Once again thank you very much for the efforts you have made to clear my ignorance.

yours
dr.s.raghunathan
Re: Select the Multi Rows of Column in single Row. [message #345906 is a reply to message #345888] Fri, 05 September 2008 04:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Barbara is just using that as a quick way to generate lot of more or less random data
Re: Select the Multi Rows of Column in single Row. [message #346004 is a reply to message #345906] Fri, 05 September 2008 09:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Like Frank said, I was just inserting values from a data dictionary table in order to fill up the table, so that I could test how it performs with more than just a few rows of data. You should not do that on your tables. I then showed that the query was able to use the index that exists as a result of creating the primary and foreign keys to run the query efficiently. You do not need to create any additional indexes. The index range scan is what the Oracle optimizer does to extract the data. The minimum that you need to understand is that if you run the code below, then the select statement at the bottom should be fast.

CREATE OR REPLACE FUNCTION vounos
  (p_table2_id IN table2.table2_id%TYPE,
   p_delim     IN VARCHAR2)
  RETURN VARCHAR2
AS
  v_result VARCHAR2 (32767);
BEGIN
  FOR r IN 
    (SELECT table2_vouno 
     FROM   table2 
     WHERE  table2_id = p_table2_id) 
  LOOP
    v_result := v_result || p_delim || r.table2_vouno;
  END LOOP;
  RETURN LTRIM (v_result, p_delim);  
END vounos;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TABLE1')
EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TABLE2')
CREATE OR REPLACE VIEW vounos_view AS
SELECT table1.table1_id, vounos (table1.table1_id, '/') AS table2_vounos
FROM   table1
/
-- this should be fast:
SELECT * FROM vounos_view WHERE table1_id BETWEEN 1001 AND 1003
/

Re: Select the Multi Rows of Column in single Row. [message #346048 is a reply to message #346004] Fri, 05 September 2008 13:38 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
@Barbara Boehmer - just want to ask on how do you find those undocumented functions/features? I think TRIM is undocumented also?
Re: Select the Multi Rows of Column in single Row. [message #346050 is a reply to message #346048] Fri, 05 September 2008 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TRIM
LTRIM
RTRIM
All in SQL Reference.
Nothing undocumented in Barbara's posts (but wm_concat function).

Regards
Michel

[Updated on: Fri, 05 September 2008 14:02]

Report message to a moderator

Re: Select the Multi Rows of Column in single Row. [message #346051 is a reply to message #346050] Fri, 05 September 2008 14:09 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks Michel, but how do they find it? Do they just randomly think of a function name then try it? A bit OT here, but anyways thanks again =)
Re: Select the Multi Rows of Column in single Row. [message #346053 is a reply to message #346051] Fri, 05 September 2008 14:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
ehegagoka wrote on Fri, 05 September 2008 12:09

Do they just randomly think of a function name then try it?



Believe it or not, some folks actually Read The Fine Manual to see what Oracle has produced & documented.

EVERY major release has a New Features manual which highlights the new capabilities which have been added.

RTFM works much better than trial & error which too many posters here embrace!
Re: Select the Multi Rows of Column in single Row. [message #346055 is a reply to message #346053] Fri, 05 September 2008 14:41 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, I'm not after the documented features (which is yes, it has a new features part), I'm curious on the "undocumented", is the undocumented one's mean they may exists in some version and not in others? or what are the other risk in it?
Re: Select the Multi Rows of Column in single Row. [message #346063 is a reply to message #346055] Fri, 05 September 2008 15:56 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
ehegagoka wrote on Fri, 05 September 2008 12:41
Hi, I'm not after the documented features (which is yes, it has a new features part), I'm curious on the "undocumented", is the undocumented one's mean they may exists in some version and not in others? or what are the other risk in it?



So you want to see the documentation on the undocumented procedures to know their capabilities & limitations. Right?

I hope you have better things to be worry about than all the undocumented code which may or may not exist within Oracle RDBMS.
Re: Select the Multi Rows of Column in single Row. [message #346085 is a reply to message #346048] Fri, 05 September 2008 18:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
ehegagoka wrote on Fri, 05 September 2008 11:38
@Barbara Boehmer - just want to ask on how do you find those undocumented functions/features?



There are some people who examine each new release very thoroughly and compare it to the previous release and/or the documentation. They may, for example, select from data dictionaries like all_source in one version and select the same from another version using a database link and minus to find the differences. Or they may just scroll through an ordered list and look for anything they haven't seen before and/or compare to something like the pl/sql packages and types reference in the online documentation. As people discover these things, they get posted on the internet in various places. Some people accumulate and post lists of such things, for the sake of entertainment, trivia, or reference. If you search the internet for undocumented Oracle you will find various such lists.

I typically stumble across such things when somebody posts the newest, simplest, but not yet documented version of something that we've all been doing a harder way. I believe I first saw the wm_concat function posted by Laurent Schneider on the OTN forums. All undocumented features are not supported. They may have bugs and may not be continued in future versions. So, they should not be used in production systems. If you are just trying to do a one-time run of something, it may be a quick way to get what you want. Usually these things are "floated". They are features that Oracle is considering for future versions, but have not yet been thoroughly tested. So, they "float" them in the current version, so they can be tested, but do not provide documentation or support for them. They may keep them and document them in future versions or change them or do away with them. You never know which they will do. I remember when the nvl2 function was undocumented, but it is now a documented feature. I am not sure if the reverse function is documented now or not. There is currently a utl_match package that has some nice string comparison functions using the Levenshtein distance and Jaro_Winkler algorithms. So, these things give you some advanced idea of what the future may hold.





Re: Select the Multi Rows of Column in single Row. [message #346140 is a reply to message #346085] Sat, 06 September 2008 08:33 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
@Barbara - Thank you very much =)
Previous Topic: Question on CURSOR re-use
Next Topic: Query about SQl
Goto Forum:
  


Current Time: Sun Dec 04 16:37:42 CST 2016

Total time taken to generate the page: 0.16300 seconds