Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: formating the nestedtable select output

Re: formating the nestedtable select output

From: Chris L. <diversos_at_uol.com.ar>
Date: Thu, 21 Jun 2007 06:15:42 -0700
Message-ID: <1182431742.793293.101300@u2g2000hsc.googlegroups.com>


On 20 jun, 16:10, Lucas Carvalho Teixeira <lucascarva..._at_gmail.com> wrote:
> On 20 jun, 15:55, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Jun 20, 1:29 pm, Lucas Carvalho Teixeira <lucascarva..._at_gmail.com>
> > wrote:
>
> > > On 20 jun, 15:12, Lucas Carvalho Teixeira <lucascarva..._at_gmail.com>
> > > wrote:
>
> > > > On 20 jun, 15:08, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > > On Jun 20, 12:46 pm, Lucas Carvalho Teixeira <lucascarva..._at_gmail.com>
> > > > > wrote:
>
> > > > > > CREATE TYPE Course AS OBJECT (
> > > > > > course_no NUMBER(4),
> > > > > > title VARCHAR2(35),
> > > > > > credits NUMBER(1));
>
> > > > > > CREATE TYPE CourseList AS TABLE OF Course;
>
> > > > > > CREATE TABLE department (
> > > > > > name VARCHAR2(20),
> > > > > > director VARCHAR2(20),
> > > > > > office VARCHAR2(20),
> > > > > > courses CourseList)
> > > > > > NESTED TABLE courses STORE AS courses_tab;
>
> > > > > > INSERT INTO department
> > > > > > VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
> > > > > > CourseList(Course(1000, 'General Psychology', 5),
> > > > > > Course(2100, 'Experimental Psychology', 4),
> > > > > > Course(2200, 'Psychological Tests', 3),
> > > > > > Course(2250, 'Behavior Modification', 4),
> > > > > > Course(3540, 'Groups and Organizations', 3),
> > > > > > Course(3552, 'Human Factors in Busines', 4),
> > > > > > Course(4210, 'Theories of Learning', 4),
> > > > > > Course(4320, 'Cognitive Processes', 4),
> > > > > > Course(4410, 'Abnormal Psychology', 4)));
>
> > > > > > I just want to remove the "CourseList" and "Course" words from my
> > > > > > select output.
> > > > > > I can do it on my app but it will require a high computer processing.
> > > > > > Cuz the select output can return to me more than 1000 rows.
> > > > > > So I want to know if it is possible to do into the oracle server.
>
> > > > > > When I do the SELECT * FROM department;
>
> > > > > > The "courses" fields returns:
> > > > > > COURSELIST(COURSE(1000, 'General Psychology', 5), COURSE(2100,
> > > > > > 'Experimental Psychology', 4), COURSE(2200, 'Psychological Tests', 3),
> > > > > > COURSE(2250, 'Behavior Modification', 4), COURSE(3540, 'Groups and
> > > > > > Organizations', 3), COURSE(3552, 'Human Factors in Busines', 4),
> > > > > > COURSE(4210, 'Theories of Learning', 4), COURSE(4320, 'Cognitive
> > > > > > Processes', 4), COURSE(4410, 'Abnormal Psychology', 4))
>
> > > > > > But I need to return this:
> > > > > > ((1000, 'General Psychology', 5), (2100, 'Experimental Psychology',
> > > > > > 4), (2200, 'Psychological Tests', 3), (2250, 'Behavior Modification',
> > > > > > 4),(3540, 'Groups and Organizations', 3), (3552, 'Human Factors in
> > > > > > Busines', 4),(4210, 'Theories of Learning', 4), (4320, 'Cognitive
> > > > > > Processes', 4),(4410, 'Abnormal Psychology', 4))
>
> > > > > Did not the original answer appeal to you? It's found here, if you
> > > > > have forgotten about your original post of this very same question:
>
> > > > >http://groups.google.com/group/comp.databases.oracle.server/browse_th...
>
> > > > > David Fitzjarrell
>
> > > > I was talking with DA Morgan, but he could not help me! :(
>
> > > > Sorry about to create a new post.
> > > > But I wanted to explain it better
>
> > > > Btw Do you know how to solve this problem?
>
> > > > Thank you
>
> > > I was reading and trying to use TABLE() into the SELECT sql to unested
> > > the nested table.
> > > But it is not returning like I want.
> > > I just need to know how to delete the "CourseList" and "Course".- Hide quoted text -
>
> > > - Show quoted text -
>
> > Apparently you can't, directly. I personally see no problem with the
> > output generated by the TABLE() function:
>
> > SQL> select t1.name, t1.director, t1.office, t2.*
> > 2 from department t1, table(t1.courses) t2;
>
> > NAME DIRECTOR OFFICE
> > COURSE_NO TITLE
> > CREDITS
> > -------------------- -------------------- --------------------
> > ---------- -----------------------------------
> > ----------
> > Psychology Irene Friedman Fulton Hall 133
> > 1000 General Psychology
> > 5
> > Psychology Irene Friedman Fulton Hall 133
> > 2100 Experimental Psychology
> > 4
> > Psychology Irene Friedman Fulton Hall 133
> > 2200 Psychological Tests
> > 3
> > Psychology Irene Friedman Fulton Hall 133
> > 2250 Behavior Modification
> > 4
> > Psychology Irene Friedman Fulton Hall 133
> > 3540 Groups and Organizations
> > 3
> > Psychology Irene Friedman Fulton Hall 133
> > 3552 Human Factors in Busines
> > 4
> > Psychology Irene Friedman Fulton Hall 133
> > 4210 Theories of Learning
> > 4
> > Psychology Irene Friedman Fulton Hall 133
> > 4320 Cognitive Processes
> > 4
> > Psychology Irene Friedman Fulton Hall 133
> > 4410 Abnormal Psychology
> > 4
>
> > 9 rows selected.
>
> > Setting proper breaks to eliminate 'redundant' output:
>
> > SQL> break on name on director on office skip 1
> > SQL> /
>
> > NAME DIRECTOR OFFICE
> > COURSE_NO TITLE
> > CREDITS
> > -------------------- -------------------- --------------------
> > ---------- -----------------------------------
> > ----------
> > Psychology Irene Friedman Fulton Hall 133
> > 1000 General Psychology
> > 5
>
> > 2100 Experimental Psychology
> > 4
>
> > 2200 Psychological Tests
> > 3
>
> > 2250 Behavior Modification
> > 4
>
> > 3540 Groups and Organizations
> > 3
>
> > 3552 Human Factors in Busines
> > 4
>
> > 4210 Theories of Learning
> > 4
>
> > 4320 Cognitive Processes
> > 4
>
> > 4410 Abnormal Psychology
> > 4
>
> > 9 rows selected.
>
> > Which may be more to your liking.
>
> > David Fitzjarrell
>
> I can not use TABLE() it is not the output that I want.
> I am going to filter this output using sed. It is not the best way but
> will work to me.
> And I will post the result here soon.
> But the problem is: the clients will have high text processing, not
> the server.
> Thank you- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Sorry if I'm being obvious but wouldn't the function REPLACE work for you. REPLACE undesired strings with null. And TRANSLATE the ()'s into {}'s. Received on Thu Jun 21 2007 - 08:15:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US