Advanced SQL by Example

==============================
Index
==============================
WITH Example
Split List separated by ',' into elements.
SUM on CASE output
Retrieve numbers from mixed text
SELECT IN with multiple columns
GLOBAL TEMPORARY Table
INSERT ALL

==============================
WITH Example
==============================

In this example, the SQL is querying table COUNTERS_HISTORY, returning the span of consecutive eight time frames having the most counters, i.e. the peak time in counters.

 SELECT row_id,
        last_modified AS peak_start_time,
last_modified+1/12 AS peak_end_time,
        counter_delta_sum
  FROM(  
  WITH static_day_view 
  AS(
  SELECT ROWNUM AS row_id,
         static_id, 
         counter_delta, 
         last_modified
    FROM (
         SELECT static_id, 
                counter_delta, 
                last_modified
           FROM COUNTERS_HISTORY  
          WHERE last_modified > TO_DATE('23042014','DDMMYYYY')
            AND last_modified < TO_DATE('24042014','DDMMYYYY')
            AND event_id = 112233
          ORDER BY last_modified ASC 
         )
    )
  SELECT DAY_VW_1.row_id AS row_id, 
         DAY_VW_1.ts_last_modified AS ts_last_modified,
         DAY_VW_1.counter_delta , 
 DAY_VW_2.counter_delta, 
 DAY_VW_3.counter_delta,
 DAY_VW_4.counter_delta,
 DAY_VW_5.counter_delta,
 DAY_VW_6.counter_delta,
  DAY_VW_7.counter_delta,
 DAY_VW_8.counter_delta,   
 DAY_VW_1.counter_delta + DAY_VW_2.counter_delta + DAY_VW_3.counter_delta +
         DAY_VW_4.counter_delta + DAY_VW_5.counter_delta + DAY_VW_6.counter_delta +
         DAY_VW_7.counter_delta + DAY_VW_8.counter_delta  AS counter_delta_sum
  FROM static_day_view DAY_VW_1,
       static_day_view DAY_VW_2,
       static_day_view DAY_VW_3,
       static_day_view DAY_VW_4,
       static_day_view DAY_VW_5,
       static_day_view DAY_VW_6,
       static_day_view DAY_VW_7,
       static_day_view DAY_VW_8   
WHERE DAY_VW_1.row_id +1 = DAY_VW_2.row_id
  AND DAY_VW_1.row_id +2 = DAY_VW_3.row_id
  AND DAY_VW_1.row_id +3 = DAY_VW_4.row_id
  AND DAY_VW_1.row_id +4 = DAY_VW_5.row_id
  AND DAY_VW_1.row_id +5 = DAY_VW_6.row_id
  AND DAY_VW_1.row_id +6 = DAY_VW_7.row_id
  AND DAY_VW_1.row_id +7 = DAY_VW_8.row_id
  ORDER BY counter_delta_sum DESC
  )
  WHERE ROWNUM < 2
  

==============================
Split List separated by ',' into elements
==============================
SELECT REGEXP_SUBSTR(ELEMENTS_LIST, '[^,]+', 1, level) AS ELEMENTS
  FROM (SELECT '22,33,44,55' AS ELEMENTS_LIST   FROM DUAL)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(ELEMENTS_LIST, '[^,]')) + 1;

==============================
SUM on CASE output
==============================
SELECT 
SUM(CASE WHEN TABLE_A.update_date < (SYSDATE-365) THEN 1 ELSE 0 END) AS OLD_UPDATED,
SUM(CASE WHEN TABLE_A.create_date < (SYSDATE-365) THEN 1 ELSE 0 END) AS OLD_CREATED
FROM TABLE_A

==============================
Retrieve numbers from mixed text
==============================

SELECT 
  max_segment_blocks, 
  TO_NUMBER(REGEXP_REPLACE (max_segment_blocks, '[^0-9]+','')) max_segment_blocks_num
  FROM SEGMENT_USAGE_HIST;

MAX_SEGMENT_BLOCKS     MAX_SEGMENT_BLOCKS_NUM
---------------------- ----------------------
     200,352 blocks                    200352
     424,960 blocks                    424960
       4,992 blocks                      4992
       1,152 blocks                      1152
   1,292,632 blocks                   1292632
          96 blocks                        96
     401,408 blocks                    401408
     266,240 blocks                    266240
   2,842,624 blocks                   2842624
   1,794,048 blocks                   1794048


==============================
SELECT IN with multiple columns
==============================

SELECT * 
  FROM MY_TABLE_A 
 WHERE (COL_A, COLB_B) NOT IN (SELECT col_1, col_2 

                        FROM MY_TABLE_B);


==============================
GLOBAL TEMPORARY Table

==============================

The data in a global temporary table is private, so that data inserted by a session can only be accessed by that session.

When session ends – the data is cleaned up.

CREATE GLOBAL TEMPORARY TABLE MY_TEMP_DATA

 (code         VARCHAR2(10), 

  id           NUMBER, 

  process_name VARCHAR2(100) ) ON COMMIT PRESERVE ROWS;

==============================
INSERT ALL

==============================

A way to split output of a singe SELECT to multiple INSERT statements.

INSERT ALL

  INTO MY_TEMP_DATA(code, id, process_name) 

  VALUES ('E164', process_name, counter_name)

  INTO MY_TEMP_DATA(code, id, process_name) 

  VALUES ('E214', counter_sum, counter_name)

  INTO MY_TEMP_DATA(code, id, process_name) 

  VALUES ('BP', counter_delta, customer_id)

SELECT  process_name, counter_sum, counter_name,counter_delta, customer_id 

  FROM SPARX_COUNTER_DATA SOURCE_DATA

 WHERE 1=1

   AND SOURCE_DATA.customer_id = '055'

   AND SOURCE_DATA.date_of_call = '20170807'

   AND SOURCE_DATA.interval_id = 1;