[PL\SQL] Increasing results

data

MNTH SM 1 10 2 20 3 30 4 40

WITH t AS (
SELECT 01 mnth,10 sm FROM dual UNION ALL
SELECT 02, 20 FROM dual UNION ALL
SELECT 03, 30 FROM dual UNION ALL
SELECT 04, 40 FROM dual
)
SELECT mnth, sm,
       SUM(sm) over(ORDER BY mnth ROWS UNBOUNDED PRECEDING) FROM t;

result

MNTH SM TOTAL 1 10 10 2 20 30 3 30 60 4 40 100

[PL\SQL] Pivot Query

data

mnth CITY SUMMA 1 11 1 1 22 25 1 33 1 2 11 52 2 22 3 2 33 3 3 11 3 3 22 65 3 33 6

WITH t AS
(
SELECT 1 mnth, 11 city, 1 summa FROM dual UNION ALL
SELECT 1 mnth, 22 city, 25 summa FROM dual UNION ALL
SELECT 1 mnth, 33 city, 1 summa FROM dual UNION ALL
SELECT 2 mnth, 11 city, 52 summa FROM dual UNION ALL
SELECT 2 mnth, 22 city, 3 summa FROM dual UNION ALL
SELECT 2 mnth, 33 city, 3 summa FROM dual UNION ALL
SELECT 3 mnth, 11 city, 3 summa FROM dual UNION ALL
SELECT 3 mnth, 22 city, 65 summa FROM dual UNION ALL
SELECT 3 mnth, 33 city, 6 summa FROM dual
) select city
      , sum(decode(mnth, 1, summa, 0)) month1
      , sum(decode(mnth, 2, summa, 0)) month2
      , sum(decode(mnth, 3, summa, 0)) month3
      , sum(sum(summa)) over(partition by city) total
  from t
 group by city;

result

/ mnth mnth mnth … mnth12 city 1 2 3 … 12 11 1 52 3 … x 22 25 3 65 … y 33 1 3 6 … z

[PL\SQL] How to Join on top (First) row

SELECT *
  FROM (SELECT ROWNUM t FROM DUAL CONNECT BY LEVEL < 6)
  JOIN (SELECT v, ROW_NUMBER() OVER(PARTITION BY v ORDER BY 1) rwnm
                     FROM (SELECT 1 v
                              FROM DUAL
                            UNION ALL
                            SELECT 1 FROM DUAL
                            UNION ALL
                            SELECT 2 FROM DUAL)) ON t = v
                                                    AND rwnm = 1;

performance

DECLARE
 l_start NUMBER;
 l_end   NUMBER;
BEGIN
 ...
 l_start := dbms_utility.get_time;
 ...
 l_end   := round((dbms_utility.get_time - l_start) / 100, 2);
 dbms_output.put_line(l_end || ' seconds...');
END;

(PL\SQL) How to get MAX(num)+DISTINCT str

CLEAR;
with t as
 (
 select '11' num, 'string1' str, '1' DATA from dual union all
 select '12' num, 'string1' str, '3' DATA from dual union all
 select '15' num, 'string1' str, '3' DATA from dual union all
 select '25' num, 'string2' str, '10' DATA from dual union all
 select '26' num, 'string2' str, '2' DATA from dual union all
 SELECT '29' num, 'string2' str, '4' DATA from dual
 )
SELECT * FROM t
WHERE num IN (SELECT MAX(num) FROM t tt WHERE t.str = tt.str);

(PL\SQL) How to parse a full name into first, middle and last names?

1st Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select ' fabc     iabc     oabcd   ' fio from dual union all
select 'fabc iabc    ' fio from dual union ALL
select '  fabc   ' fio from dual union ALL
SELECT 'fabc  iabc  ' fio from dual
)
SELECT substr(fio, 1, instr(fio, ' ')) AS LastName,
       substr(fio, instr(fio, ' ') + 1,
       instr(substr(fio, instr(fio, ' ') + 1), ' '))
       FirstName,
       substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1), ' ') + 1,
       instr(substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1),
       ' ') + 1), ' ')) MiddleName
  FROM
  (
  SELECT
    TRIM(REPLACE(
         REPLACE(REPLACE(fio, ' ', '@!'), '!@'),
         '@!', ' '))
    || ' ' fio FROM t) t;

2nd Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '       fabc iabc     oabcd   ' fio from dual union ALL
select 'fabc     iabc     oabcd   ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select 'fabc iabc ' fio from dual union ALL
SELECT 'fabc  iabc' fio from dual)
SELECT
trim(substr(trim(fio),1,instr(trim(fio),' ',1,1)))
      AS LastName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,instr
         (TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
         length(trim(fio))))
    ELSE trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1))) END AS FirstName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))), 1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN 'null'
     ELSE trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
          length(trim(fio)))) END AS MiddleName FROM t;

ResultSet

LASTNAME	FIRSTNAME	MIDDLENAM
——–	———	———
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
Baimendinov	Salimjan	Danauly
fabc	iabc	null
fabc	iabc	null

Till New Year

SELECT 'Till the New Year ' ||
to_char(extract(YEAR FROM SYSDATE) + 1) || ' - ' ||
           trunc(months_between(to_date('01.01.' ||
to_char(extract(YEAR FROM SYSDATE) + 1), 'dd.mm.yyyy'), SYSDATE))
           || ' months ' ||
to_char(LAST_DAY(SYSDATE) - SYSDATE) || ' day ' ||
to_char(23 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 1, 2)))
           || ' hh ' ||
to_char(59 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 4, 2)))
           || ' mi ' ||
to_char(59 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 7, 2)))
           || ' ss ' || '[today:' ||
to_char(SYSDATE, 'mm:dd:yyyy hh24:mi:ss') || ']' AS "Happy New Year!!!"
FROM dual;

[PL\SQL] Convert seconds to Day hour : min : sec

variable vSec number;
exec :vSec := 68400;
SELECT
to_char(trunc(:vSec / 60 / 60 / 24), '09') || ' [day]' ||
to_char(trunc(MOD(:vSec / 60 / 60, 24)), '09') ||' [hh24]' ||
to_char(trunc(MOD(:vSec, 3600) / 60), '09') || ' [mi]' ||
to_char(MOD(MOD(:vSec, 3600), 60), '09') || ' [ss]'
FROM dual;

Result in one Line

with t as (
select 'string1' str from dual union all
select 'string2' str from dual union all
select 'string3' str from dual union all
select 'string4' str from dual union all
select 'string5' str from dual union all
SELECT 'string6' str from dual
)
select '~'||strs||'~'  AS oneline from
( select ltrim(sys_connect_by_path(str, '~'), '~') as strs
from
( select str, lag(str) over (order by str) as prev_str
from t
)
start with prev_str is null
connect by prev_str = prior str
order by 1 desc
)
where rownum = 1;

Result set

SQL>
oneline
--------------------------------------------------
~string1~string2~string3~string4~string5~string6~

using

...
where
Instr('~string1~string2~string3~string4~string5~string6~',
'~'||trim(rtrim(t.city))||'~')  <> 0
...

Index info oracle

SELECT a.index_name,  b.column_name,
           a.index_type, a.table_name
  FROM USER_INDEXES a, USER_IND_COLUMNS b
 WHERE a.index_name = b.index_name
       AND a.table_name = upper('tDocument');