Tip

Dynamic time sequence query

Suppose you need to see a full (10 minutes, half-hour, one hour, etc) time map for a given date with user_objects changes counter. And you need to do this in one SELECT statement, with no additional tables, views, packages, procedures, or functions. Is it possible? Yes, it is! Here's how:

  
------------------------------------------------------------------------------- 
-- @DYNAMIC_TIME_QUERY.SQL 
------------------------------------------------------------------------------- 
-- Author:  Dusan Djuric 
-- mail:    dusan.djuric@iii.hr 
-- http:    www.CroGuide.com 
-- 
-- Date:    24.01.2002 
-- 
-- Usage:   
--          1. Start SQL*Plus at directory where you put this script 
--          2. Connect to your schema 
--          2. At SQL prompt type "@DYNAMIC_TIME_QUERY.SQL" 
-- 
-- 
-- Purpose: Time-sequences query with no additional packages, procedures, 
-- or functions. 
-- 
-- Tested environment: 
--                    SQL*Plus: Release 8.1.7.0.0 
--                    Oracle8i  Release 8.1.7.2.1 
--                    Windows 98 SE client 
-- 
-- Explanations: 
-- 
--          This trick is based on these facts: 
--          1. One day has 86400 seconds so we need 1 to 5 figures 
--             number to generate all posible seconds of 1 day 
--          2. SELECT statement gives all row combinations
--             if tables are not joined in WHERE clause. 
--          3. Powerful MOD function (read Oracle reference) 
--             enables any wished increment of base number (time sequence 
--             in seconds) 
--          
--          I use the european DATE format. I didn't try any other. 
-- 
------------------------------------------------------------------------------- 

set linesize 300 
set pagesize 1000 
set long 2000 
set longchunksize 2000 
set numwidth 20 
set arraysize 10 
set maxdata 60000 

-- This is for proper date output format 
alter session set NLS_DATE_FORMAT = 'DD.MM.RRRR HH24:MI:SS'; 

-- VARIABLES 
VARIABLE START_TIME    CHAR(8); 
VARIABLE END_TIME      CHAR(8); 
VARIABLE TIME_FORMAT   VARCHAR2(10); 
VARIABLE TIME_SEQUENCE NUMBER; 
VARIABLE ENY_DATE      CHAR(10); 
VARIABLE DFORMAT       VARCHAR2(23); 

------------------------------------------------------------------------------- 
-- EXAMPLE 1 ---------------- SIMPLE TIME SEQUENCE QUERY ---------------------- 
------------------------------------------------------------------------------- 
--          This example gives 13 seconds time intervals between 15:53:13 
--          and 16:01:25 on 15.01.2001 
-- 
--          Be aware of :END_TIME := '16:01:25' setter. 
--          This interval is not in query output because the last 13 
--          seconds interval is bigger than :END_TIME limiter. 
-- 
--          If you set :TIME_FORMAT := 'HH24:MI' instead of 'HH24:MI:SS' 
--          you'll get just 1 minute intervals 
------------------------------------------------------------------------------- 

PROMPT -- EXAMPLE 1 ---------------- SIMPLE TIME SEQUENCE QUERY --------------- 

-- SETING VARIABLES 
BEGIN 
  :START_TIME    := '15:53:13';   -- Time format HH24:MI:SS 
  :END_TIME      := '16:01:25';   -- Time format HH24:MI:SS 
  :TIME_FORMAT   := 'HH24:MI:SS'; -- Time string formats HH24 , HH24:MI 
                                  -- or HH24:MI:SS for output 
  :TIME_SEQUENCE := 13;           -- always in seconds, e.g. 60*10 is 
                                  -- 10 minutes interval sequence 
  :ENY_DATE      := '15.01.2001'; -- eny date as CHAR in DD.MM.RRRR format 
END; 
/ 

-- COLUMN FORMATING 
column TSTRING format a10 heading 'TIME|(varchar2)' 
column TDATE   format a21 heading 'DTIME|(date)' 

SELECT UNIQUE 
--Time representation (string and date data type) 
  TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT) TSTRING, 
  TO_DATE(:ENY_DATE||' '||TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT),'DD.MM.RRRR '||:TIME_FORMAT) TDATE

FROM 
  ( 
    SELECT 
--Building numbers by length restriction 
      TO_NUMBER(DECODE(LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))), 

                       1,N1.N, 
                       2,N1.N||N2.N, 
                       3,N1.N||N2.N||N3.N, 
                       4,N1.N||N2.N||N3.N||N4.N, 
                       5,N1.N||N2.N||N3.N||N4.N||N5.N 
                      ) 
               ) SEC 
    FROM 
--5 number combinations 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >=

    Requires Free Membership to View

1 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 ) N1, ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 ) N2, ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 ) N3, ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 ) N4, ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 ) N5 ) INTERVAL WHERE -- No joins for N1-N5 subquerys to get 5 number combinations -- Min/Max number restriction INTERVAL.SEC <= ( (TO_NUMBER(SUBSTR(:END_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:END_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:END_TIME,7,2))) - (TO_NUMBER(SUBSTR(:START_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:START_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:START_TIME,7,2))) ) AND -- See MOD function reference MOD((INTERVAL.SEC + :TIME_SEQUENCE),:TIME_SEQUENCE) = 0 ORDER BY TDATE; PROMPT -- END / EXAMPLE 1 ---------- SIMPLE TIME SEQUENCE QUERY --------------- PROMPT ------------------------------------------------------------------------------- -- EXAMPLE 2 ------------ CHANGED USER OBJECTS half-hour TIME MAP ------------- ------------------------------------------------------------------------------- -- This example count changed objects on half-hour time intervals -- between 00:00:00 and 23:59:59 on user shema objects last -- date changes ------------------------------------------------------------------------------- PROMPT PROMPT PROMPT -- EXAMPLE 2 ---------- CHANGED USER OBJECTS half-hour TIME MAP -------- -- SETING VARIABLES BEGIN :START_TIME := '00:00:00'; :END_TIME := '23:59:59'; :TIME_FORMAT := 'HH24:MI:SS'; :TIME_SEQUENCE := 60*30; -- HALF HOUR -- user_object changes last date SELECT TO_CHAR(MAX(LAST_DDL_TIME),'DD.MM.RRRR') INTO :ENY_DATE FROM USER_OBJECTS; END; / -- COLUMN FORMATING column TSTRING format a10 heading 'TIME|(varchar2)' column TDATE format a21 heading 'DTIME|(date)' column ALTERED format a7 heading 'ALTERED|' SELECT TSEQ.TSTRING TSTRING, TSEQ.TDATE TDATE, DECODE(COUNT(O.OBJECT_ID),0,NULL,COUNT(O.OBJECT_ID)) ALTERED FROM ( SELECT UNIQUE --Time representation (string and date data type) TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT) TSTRING, TO_DATE(:ENY_DATE||' '||TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT),'DD.MM.RRRR '||:TIME_FORMAT) TDATE FROM ( SELECT --Building numbers by length restriction TO_NUMBER(DECODE(LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))), 1,N1.N, 2,N1.N||N2.N, 3,N1.N||N2.N||N3.N, 4,N1.N||N2.N||N3.N||N4.N, 5,N1.N||N2.N||N3.N||N4.N||N5.N ) ) SEC FROM --5 number combinations ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 ) N1, ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 ) N2, ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 ) N3, ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 ) N4, ( SELECT TO_NUMBER(NULL) N FROM DUAL UNION SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 ) N5 ) INTERVAL WHERE -- No joins for N1-N5 subquerys to get 5 number combinations -- Min/Max number restriction INTERVAL.SEC <= ( (TO_NUMBER(SUBSTR(:END_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:END_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:END_TIME,7,2))) - (TO_NUMBER(SUBSTR(:START_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:START_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:START_TIME,7,2))) ) AND -- See MOD function reference MOD((INTERVAL.SEC + :TIME_SEQUENCE),:TIME_SEQUENCE) = 0 ) TSEQ, USER_OBJECTS O WHERE TRUNC(O.LAST_DDL_TIME(+),'MI') BETWEEN TSEQ.TDATE-(:TIME_SEQUENCE/(24*60*60)) AND TSEQ.TDATE GROUP BY TSEQ.TSTRING, TSEQ.TDATE; PROMPT -- END / EXAMPLE 2 ------ CHANGED USER OBJECTS half-hour TIME MAP ------ PROMPT

This was first published in April 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.