RE: SQL Question

From: Dmitriy M. Labutin <Dmitriy_Labutin_at_ep.minsk.by>
Date: Thu, 4 Jan 2001 11:23:36 +0200
Message-Id: <10731.125682@fatcity.com>

Hi,

Unfortunately you can not use user defined group by functions, but workaround exists:  

---

create table FL(DT DATE,SQ NUMBER,A_FROM VARCHAR2(100), A_TO VARCHAR2(100)); insert into FL VALUES(TO_DATE('2000-01-01','YYYY-MM-DD'),1,'A','B');

insert into FL VALUES(TO_DATE('2000-01-02','YYYY-MM-DD'),1,'C','D'); insert into FL VALUES(TO_DATE('2000-01-02','YYYY-MM-DD'),2,'D','E');

insert into FL VALUES(TO_DATE('2000-01-03','YYYY-MM-DD'),1,'F','G');
insert into FL VALUES(TO_DATE('2000-01-03','YYYY-MM-DD'),2,'G','H');
insert into FL VALUES(TO_DATE('2000-01-03','YYYY-MM-DD'),3,'H','I');

SELECT
   dt,a||b||c||d
FROM
   (SELECT

       dt,MAX(DECODE(sq,0,a_to,NULL)) a,
          MAX(DECODE(sq,1,'-'||a_to,NULL)) b,
          MAX(DECODE(sq,2,'-'||a_to,NULL)) c,
          MAX(DECODE(sq,3,'-'||a_to,NULL)) d
    FROM
       (SELECT 
           dt,sq,a_to 
        FROM 
           FL
        UNION ALL
        SELECT 
           dt,0,a_from 
        FROM 
           FL
        WHERE
           sq=1)
    GROUP BY 
       dt) 

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

Of course, you can extend it for any reasonable number or intermediate stops
(e,f,g and so one)

Cheers,
Dimitri

-----Original Message-----
From: Johan Locke_at_i-Commerce Services [mailto:Johan.Locke_at_za.didata.com] Sent: Thursday, January 04, 2001 9:11 AM To: Multiple recipients of list ORACLE-L Subject: SQL Question

Hi All

Got the following table

DATE 		SEQNO		AREA_FROM	AREA_TO
----		-----		---------	--------
20-JAN	1		LONDON	NEW YORK
20-JAN	2		NEW YORK	ATLANTA
21-JAN	1		LONDON	PARIS
22-JAN	1		NAIROBI	PARIS
22-JAN	2		PARIS		NAIROBI
23-JAN	1		ATLANTA	NEW YORK


Now I need the following report based on the data:
DATE		AREAS				
----		-----				
20-JAN	LONDON - NEW YORK - ATLANTA
21-JAN	LONDON - PARIS
22-JAN	NAIROBI - PARIS - NAIROBI
23-JAN	ATLANTA - NEW YORK

(1) Any Ideas on how to do this report without using Dynamic SQL
(2) Is it possible to write a custom GROUP function in PL/SQL (i.e.,
something that would do a concatenate instead of a sum)?

Kind Regards

Johan Locke

http://www.JohanLocke.co.za
Certified Oracle 8 & 8i DBA
Certified Oracle Developer

Dimension Data i-Commerce Internet Services Direct Line: +27 11 516 5343
mailto:Johan.Locke_at_za.didata.com
http://www.didata.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johan Locke_at_i-Commerce Services
  INET: Johan.Locke_at_za.didata.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

Received on Thu Jan 04 2001 - 03:23:36 CST