Re: tricky SQL query (currently handled through PL/SQL)

From: Ralf <db.2.gemini_at_spamgourmet.com>
Date: 11 Jun 2003 23:48:03 -0700
Message-ID: <51a9e702.0306112248.16458ca5_at_posting.google.com>

Hello Jetlag,

I hope, that this could help you. I find a similar solution some times ago
by the cool site from Thomas Kyte (asktom.oracle.com).

SQL> select * from t2;

   MY_NAME MY_DATE                MY_CODE
         1 01.01.2003 00:00:00          1
         1 01.01.2003 00:00:01          1
         1 01.01.2003 00:00:04          1
         1 01.01.2003 00:00:05          3
         1 01.01.2003 00:00:09          2
         1 01.01.2003 00:00:15          2
         1 01.01.2003 00:00:20          1
         1 01.01.2003 00:00:24          1
         2 01.01.2003 00:00:00          1
         2 01.01.2003 00:00:03          1

SQL> SELECT max(my_name) mn
  2        ,max(decode(mod(rn,2),1,my_date,NULL)) x_start
  3        ,max(decode(mod(rn,2),0,my_date,NULL)) x_end
  4        ,max(my_code) mc

  5 FROM (
  6 SELECT v.*
  7 ,ROWNUM rn
  8 FROM (

  9  SELECT v1.my_name
 10        ,v1.my_date
 11        ,v1.my_code
 12        ,nvl(lag(v1.my_name||v1.my_code) OVER (ORDER BY
v1.my_Name,v1.my_date),'-1') before
 13        ,nvl(lead(v1.my_name||v1.my_code) OVER (ORDER BY
v1.my_name,v1.my_date),'-1') after
 14        ,d.x
 15  FROM t2 v1,(SELECT 1 x FROM dual
 16              UNION ALL
 17              SELECT 2 x FROM dual) d) v

 18 WHERE (v.my_name||v.my_code <> v.before AND v.x=1) OR (v.my_name||v.my_code <> v.after AND v.x=2)  19 )
 20 GROUP BY ceil(rn/2)
 21 ;

        MN X_START             X_END                       MC
         1 01.01.2003 00:00:00 01.01.2003 00:00:04          1
         1 01.01.2003 00:00:05 01.01.2003 00:00:05          3
         1 01.01.2003 00:00:09 01.01.2003 00:00:15          2
         1 01.01.2003 00:00:20 01.01.2003 00:00:24          1
         2 01.01.2003 00:00:00 01.01.2003 00:00:03          1

SQL> Analytic functions are really great (but afaik not in Standard Edition)

Best regards

Ralf

> This should become the following in my_table_v2:
>
> 1 '01/01/03 00:00:00' '01/01/03 00:00:04' 1
> 1 '01/01/03 00:00:05' '01/01/03 00:00:05' 3
> 1 '01/01/03 00:00:09' '01/01/03 00:00:15' 2
> 1 '01/01/03 00:00:20' '01/01/03 00:00:24' 1
>
> -- jetlag --
Received on Thu Jun 12 2003 - 08:48:03 CEST