[PL\SQL] Select a random row from a database table

Friday, 5 February, 2010 Baur Leave a comment

There is some way to select a random record or row from a database table.

SELECT col1, col2, col3  FROM (SELECT * FROM tcard SAMPLE(1) ORDER BY dbms_random.VALUE)
          WHERE branch = 1
                CANCELDATE > SYSDATE
                AND ROWNUM <= 100);
Categories: PL/SQL Tags: , ,

[PL\SQL] Increasing results

Thursday, 27 August, 2009 Baur Leave a comment

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
Categories: PL/SQL Tags: ,

[PL\SQL] Pivot Query

Wednesday, 26 August, 2009 Baur Leave a comment

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
Categories: PL/SQL Tags: , ,

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

Wednesday, 26 August, 2009 Baur 1 comment
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;
Categories: PL/SQL Tags: ,

Search table

Tuesday, 18 August, 2009 Baur Leave a comment
SELECT *
  FROM user_all_tables
 WHERE TABLE_NAME LIKE UPPER('%ISSCR%')
       AND TABLE_NAME NOT IN (SELECT TABLE_NAME
                                FROM user_all_tables
                               WHERE TABLE_NAME LIKE '%$%'
                                     OR TABLE_NAME LIKE '%REP%');
Categories: Uncategorized Tags:

performance

Tuesday, 18 August, 2009 Baur Leave a comment
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;
Categories: PL/SQL, Snippets Tags: ,

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

Tuesday, 18 August, 2009 Baur 2 comments
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);
Categories: PL/SQL, Snippets Tags: ,

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

Thursday, 30 July, 2009 Baur Leave a comment

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
Categories: PL/SQL Tags: , ,

Till New Year

Thursday, 2 July, 2009 Baur Leave a comment
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;
Categories: PL/SQL Tags: , ,

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

Thursday, 2 July, 2009 Baur Leave a comment
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;
Categories: PL/SQL Tags: , ,