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;
