rmorenobello

/*
SQL SERVER >= 2005 

https://msdn.microsoft.com/en-us/library/ms186734(v=sql.110).aspx

B. Returning a subset of rows
The following example calculates row numbers for all rows in the SalesOrderHeader table in the order of the OrderDate and returns only rows 50 to 60 inclusive.
*/

-- with raw subquery

rmorenobello

-- NOTA: no recuerdo por qué todo esto en lugar de un simple join ?!?!?!

-- DO NOT DO THIS!!!
CREATE INDEX idx_customer_name ON customer (last_name, first_name);

SELECT *
FROM customer
WHERE first_name || last_name IN (
  SELECT first_name || last_name
  FROM actor

rmorenobello

-- https://dzone.com/articles/how-to-calculate-multiple-aggregate-functions-in-a?edition=297992
-- Calculate Multiple Aggregate Functions (diff WHERE conditions) in a Single Query

-- ***************** PROBLEM to solve:
-- Number of films with a given length / language_id
SELECT count(*)
FROM film
WHERE length BETWEEN 120 AND 150
AND language_id = 1;
-- Number of films with a given length / rating

rmorenobello

-- https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9531934000346471628

-- Cálculo edad / edat / age

-- Age calculation from birthday(always correct!!!!)
-- ==============================
-- NOTA: tras varias pruebas lo siguiente se entiende mejor y es correcto:
trunc(MONTHS_BETWEEN(TO_DATE(20200229,'yyyymmdd'), TO_DATE(20000229,'yyyymmdd'))/12)
--trunc() o floor()

rmorenobello

-- We test if the SCD type 2 start and end rows are consecutive

-- NOTE: in this case the last end date is always the day before the start of the next SCD register, 
-- but that seems bad practice as it wouldn't work naturally with BETWEEN startDate and endDate.

-- returns 1 (ok) when there is no prev (or next) record to test for that group (partition)
SELECT 
 id, colA, period_start_date, period_end_date 
, CASE WHEN ( last_end is null OR last_end = period_start_date - 1 ) THEN 1 else 0 END as start_date_OK
, CASE WHEN ( next_start is null OR next_start = period_end_date + 1 ) THEN 1 else 0 END as end_date_OK

rmorenobello

declare  
/* Para comprobar si una combinacion de cols es PK natural de una tabla */
    tabla varchar2(1000);
    group_by varchar2(1000); -- campos separados por comas    
begin
    tabla := 'INT_RELACIO';
    group_by := 'ENTITAT_ORIGEN, ENTITAT_DESTI, SUBTIPO_RELACIO, PERIOD_START_DATE';
    
    DBMS_OUTPUT.PUT_LINE('select /*+ PARALLEL 4 */ ' || group_by || ', count(*) FROM ' || tabla || ' group by ' || group_by || ' having count(*)>1 fetch first row only;');
end;

rmorenobello

-- ¡¡¡¡Resumenes con ejemplos de cada tema!!!!
http://www.morganslibrary.org/library.html

https://lalitkumarb.wordpress.com/2014/05/31/oracle-explain-plan/

-- EXPLAIN PLAN FOR
-- per consultar resultat:
set linesize 132;
SELECT * FROM TABLE(dbms_xplan.display);

rmorenobello

-- Estudiamos porqué hay muchisimas nominas a null (resulta que han cambiado datos origen, 20.000 sin nomina informada)

select * from mig_tsocial WHERE SEQUENCIAL = 58411 and id_carrega = 5;

  
select length, count(*) from (
    select LENGTH(DADES) as length from mig_tsocial where id_carrega = 5 
) group by length
order by length asc;
select 11122+8833+1 from dual; -- 19956

rmorenobello

http://www.grassroots-oracle.com/2015/03/listagg-to-clob-avoid-4000-chr-limit.html

https://technology.amis.nl/2015/03/13/using-an-aggregation-function-to-query-a-json-string-straight-from-sql/

-- SNIPPETS!
http://psoug.org/reference/dbms_lob.html



DEGRADACION de la sesión por creación de CLOBs internos (ABSTRACT LOBS)

rmorenobello

-- NOTA: lo optimo seria hacer BULK INSERT INTO y FOR ALL con cursores!

ALTER SESSION ENABLE PARALLEL DML;

DECLARE

PAC_ID_INI      PLS_INTEGER;
PAC_ID_INTERVAL PLS_INTEGER :=1000000 ;
PAC_ID_TOTAL    PLS_INTEGER;