Oracle-L: RE: Brain cramp on analytical functions and grouping.

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Thu, 08 Aug 2002 07:33:27 -0800
Message-ID: <F001.004AF030.20020808073327@fatcity.com>

Hey Jacques,

Yeah, I think that's about right. I was hoping that there would be a magic built-in function to translate the rows of CP into a single column -- being largely unfamiliar with the analytical functions -- but this looks to be the best viable solution.

Thanks! :)

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Wednesday, August 07, 2002 6:36 PM To: 'ORACLE-L_at_fatcity.com'
Cc: Jesse, Rich

(answer follows the original question)

You could always write a function to return all values of RC for a particular WO, but I hope that someone would come up with a better way. (see example)
SQL> describe my_data ;

 Nom                   NULL ?   Type 
 --------------------- -------- ------------------ 
 C                              CHAR(1) 
 N                              NUMBER(1) 
SQL> select * from my_data ; 
C         N 
- --------- 
A         1 
A         2 
B         1 
B         2 
B         3 
C         1 
C         2 
D         1 
D         3

9 ligne(s) sélectionnée(s).
SQL> create function get_all_n (c_in in char) return varchar2 2 is

  3     all_n varchar2 (4000) ; 
  4     cursor c_all_n (key_in varchar2) is 
  5       select n from my_data where c = key_in ; 
  6  begin 
  7     all_n := null ; 
  8     for c_all_n_rec in c_all_n (c_in) 
  9     loop 
 10        all_n := all_n || to_char (c_all_n_rec.n, 'FM0000000000') ; 
 11     end loop ; 
 12     return all_n ;

13 end get_all_n ;
14 /
Fonction créée.
SQL> column all_n format a40
SQL> select c, get_all_n (c) as all_n
2 from my_data ;
C ALL_N

- ----------------------------------------

A 00000000010000000002
A 00000000010000000002
B 000000000100000000020000000003
B 000000000100000000020000000003
B 000000000100000000020000000003
C 00000000010000000002
C 00000000010000000002
D 00000000010000000003
D 00000000010000000003
9 ligne(s) sélectionnée(s).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Aug 08 2002 - 10:33:27 CDT