Home  | Whitepapers and notes  | Using Analytic Functions in Reports | Full example scripts www.gplivna.eu on del.icio.us
add to del.icio.us

This file contains SQL statement examples for article Using Analytic Functions in Reports and can be read and understood only along with it.

Contents

Case one: persons and their citizenships. 1

Case two: peoples without valid identity cards. 3

Case three: getting the current value and first value in a hierarchy. 6

Case four: do all that in few sections. 8

Case one: persons and their citizenships

-- examples has been tested on three versions 9.2 and 10.1

-- Enterprise edition and 10.2 Express edition.

-- Although should work on other versions since 9.1 as well

set autotrace off
DROP TABLE citizenships;
DROP TABLE persons;
 CREATE TABLE persons (
   prs_id      NUMBER NOT NULL
  ,prs
_name    VARCHAR2(15) NOT NULL

  ,prs_surname VARCHAR2(15) NOT NULL
);

--CTZ_CODE simulates real world citizenships e.g. USA, UK,
--but CTZ_TYPE can contain values like Citizens, Non-citizens,
--Refugees. CTZ_TYPE will be necessary for Case two.

CREATE TABLE citizenships (
   ctz_id      NUMBER NOT NULL
  ,ctz
_prs_id  NUMBER NOT NULL

  ,ctz_code    VARCHAR2(3) NOT NULL
  ,ctz_type    VARCHAR2(1) NOT NULL
);

-- generating sample data
INSERT ALL
WHEN 1=1 THEN
  INTO persons (prs_id, prs_name, prs_surname)
  VALUES (rn, substr(object_name,1 , 15), substr(object_name,1 , 15))
-- every person has at least 1 citizenship
WHEN 1 = 1 THEN
  INTO citizenships
  VALUES (rn, rn, substr(object_type, 1, 3), 'C')
-- several have two
WHEN mod(rn, 5) = 0 AND substr(object_type, 1, 3) <> 'TAB' THEN
  INTO citizenships
  VALUES (rn + 200000, rn, 'TAB', 'C')
-- few have three
WHEN mod(rn, 15) = 0 AND substr(object_type, 1, 3) <> 'SEQ' THEN
  INTO citizenships
  VALUES (rn + 400000, rn, 'SEQ', 'C')
SELECT rownum rn, object_name, object_type FROM all_objects;

-- just to understand what has been generated
SELECT count(*) cnt, ctz_code code
FROM citizenships
GROUP BY ctz_code
ORDER BY cnt desc;

CNT COD

---- ---

5231 TAB

3575 SYN

3284 VIE

2364 TYP

2327 IND

2298 PAC

1456 SEQ

 186 TRI

 109 LIB

  86 FUN

  57 PRO

  20 OPE

  19 RUL

  13 EVA

  10 CLU

   7 CON

   4 JOB

   3 WIN

   1 DIR

   1 SCH

 

-- now just add some constraints and indexes
ALTER TABLE persons ADD CONSTRAINT prs_pk PRIMARY KEY (prs_id);
ALTER TABLE citizenships ADD CONSTRAINT ctz_pk PRIMARY KEY (ctz_id);
ALTER TABLE citizenships ADD CONSTRAINT ctz_prs_fk
FOREIGN KEY (ctz_prs_id) REFERENCES persons (prs_id);
CREATE INDEX ctz_prs_id_idx ON citizenships (ctz_prs_id);

-- set autotrace and timing on to see statistics and elapsed time
-- to get rid of query parsing and (hopefully) physical reads
-- you should run both queries several times and compare statistics
set autotrace on
set timing on
-- traditional query
SELECT
  count(distinct ctz_prs_id) cnt,
  ctz_code code
FROM citizenships
WHERE ctz_code = 'TAB'
GROUP BY ctz_code
UNION ALL
SELECT count(distinct ctz_prs_id) cnt, ctz_code code
FROM citizenships c1
WHERE ctz_code <> 'TAB'
  AND NOT EXISTS (
    SELECT 'TAB'
    FROM citizenships c2
    WHERE c1.ctz_prs_id = c2.ctz_prs_id
      AND c2.ctz_code = 'TAB'
  )
GROUP BY ctz_code
ORDER BY cnt desc;


New DATA

 

CNT COD

---- ---

5231 TAB

2876 SYN

2626 VIE

1884 TYP

1858 IND

1827 PAC

 272 SEQ

 148 TRI

  87 LIB

  71 FUN

  46 PRO

  16 OPE

  14 RUL

  12 EVA

   8 CLU

   7 CON

   3 JOB

   3 WIN

   1 DIR

   1 SCH

Old DATA, just counting citizenships

 

CNT COD

---- ---

5231 TAB

3575 SYN

3284 VIE

2364 TYP

2327 IND

2298 PAC

1456 SEQ

 186 TRI

 109 LIB

  86 FUN

  57 PRO

  20 OPE

  19 RUL

  13 EVA

  10 CLU

   7 CON

   4 JOB

   3 WIN

   1 DIR

   1 SCH


 

-- query with analytics
SELECT count(*) cnt, ctz_code
FROM (
  SELECT ctz_code,
    CASE WHEN ctz_prs_id <> prev_prs_id THEN 1
         WHEN first_code = 'TAB' THEN 0
         ELSE 1
    END flag
  FROM (
    SELECT
      ctz_prs_id,
      ctz_code,
      first_value (ctz_code) OVER (PARTITION BY ctz_prs_id ORDER BY decode (ctz_code, 'TAB', 0, 1)) first_code,
      lag(ctz_prs_id, 1, -1) OVER (ORDER BY ctz_prs_id, decode (ctz_code, 'TAB', 0, 1)) prev_prs_id
    FROM citizenships
  )
)
WHERE flag = 1
GROUP BY ctz_code
ORDER BY cnt desc;

Case two: peoples without valid identity cards

 

set autotrace off
DROP TABLE citizenships;
DROP TABLE documents;
DROP TABLE persons;
CREATE TABLE persons (
   prs_id      NUMBER NOT NULL
  ,prs_name    VARCHAR2(15) NOT NULL
  ,prs_surname VARCHAR2(15) NOT NULL
);

CREATE TABLE citizenships (
   ctz_id      NUMBER NOT NULL
  ,ctz
_prs_id  NUMBER NOT NULL

  ,ctz_code    VARCHAR2(3) NOT NULL
  ,ctz_type    VARCHAR2(1) NOT NULL
);

CREATE TABLE documents (
   doc_id            NUMBER NOT NULL
  ,doc
_prs_id        NUMBER NOT NULL

  ,doc_valid_from    DATE NOT NULL
  ,doc_valid_to      DATE NOT NULL
  ,doc_type          VARCHAR2(19)
);

-- generating sample data
INSERT ALL
WHEN 1=1 THEN
  INTO persons (prs_id, prs_name, prs_surname)
  VALUES (rn, substr(object_name,1 , 15), substr(object_name,1 , 15))
-- every person has at least 1 citizenship
-- half of them are citizens
WHEN mod(rn, 2) = 0 THEN
  INTO citizenships
  VALUES (rn, rn, substr(object_type, 1, 3), 'C')
-- half of them are non-citizens
WHEN mod(rn, 2) = 1 THEN
  INTO citizenships
  VALUES (rn, rn, substr(object_type, 1, 3), 'N')
-- several have two
WHEN mod(rn, 5) = 0 AND substr(object_type, 1, 3) <> 'TAB' THEN
  INTO citizenships
  VALUES (rn + 200000, rn, 'TAB', 'C')
-- few have three
WHEN mod(rn, 15) = 0 AND substr(object_type, 1, 3) <> 'SEQ' THEN
  INTO citizenships
  VALUES (rn + 400000, rn, 'SEQ', 'C')
-- half of all people have already expired ID card
WHEN mod(rn, 2) = 0 THEN
  INTO documents
  VALUES (rn, rn, sysdate - 100, sysdate – 10, 'Citizen ID card')
-- 2/5 of all people have valid citizen ID cards
WHEN mod(rn, 5) <= 1 THEN
  INTO documents
  VALUES (rn + 200000, rn, sysdate - 10,
          sysdate + 10, 'Citizen ID card')
-- 2/5 of all people have valid non-citizen ID cards
WHEN mod(rn, 5) BETWEEN 2 AND 3 THEN
  INTO documents
  VALUES (rn + 400000, rn, sysdate - 10,
          sysdate + 10, 'Non-citizen ID card')
-- 2/7 of all people have valid citizen ID cards
WHEN mod(rn, 7) <= 1 THEN
  INTO documents
  VALUES (rn + 600000, rn, sysdate - 10,
          sysdate + 10, 'Citizen ID card')
-- 2/7 of all people have valid non-citizen ID cards
WHEN mod(rn, 7) BETWEEN 2 AND 3 THEN
  INTO documents
  VALUES (rn + 800000, rn, sysdate - 10,
          sysdate + 10, 'Non-citizen ID card')
SELECT rownum rn, object_name, object_type FROM all_objects;

-- now just add some constraints and indexes
ALTER TABLE persons ADD CONSTRAINT prs_pk PRIMARY KEY (prs_id);
ALTER TABLE citizenships ADD CONSTRAINT ctz_pk PRIMARY KEY (ctz_id);
ALTER TABLE documents ADD CONSTRAINT doc_pk PRIMARY KEY (doc_id);
ALTER TABLE citizenships ADD CONSTRAINT ctz_prs_fk
FOREIGN KEY (ctz_prs_id) REFERENCES persons (prs_id);
CREATE INDEX ctz_prs_id_idx ON citizenships (ctz_prs_id);
ALTER TABLE documents ADD CONSTRAINT doc_prs_fk
FOREIGN KEY (doc_prs_id) REFERENCES persons (prs_id);
CREATE INDEX doc_prs_id_idx ON documents (doc_prs_id);

-- traditional query
SELECT COUNT(distinct ctz_prs_id), ctz_code, ctz_type
FROM citizenships
WHERE ctz_code = 'TAB'
  AND NOT EXISTS (
    SELECT 'TAB'
    FROM documents
    WHERE doc_valid_from < sysdate
      AND doc_valid_to > sysdate
      AND doc_prs_id = ctz_prs_id
      AND (ctz_type = 'C' AND doc_type = 'Citizen ID card' OR
           ctz_type = 'N' AND doc_type = 'Non-citizen ID card')
  )
GROUP BY ctz_code, ctz_type;

-- analytic query
SELECT count(distinct ctz_prs_id), ctz_code, ctz_type
FROM (
  SELECT ctz_prs_id, ctz_code, ctz_type, flag,
    row_number() OVER (PARTITION BY ctz_prs_id ORDER BY flag desc) rn
  FROM (
    SELECT /*+ FULL(citizenships) FULL(documents) */
      ctz_prs_id, ctz_code, ctz_type,
      CASE WHEN ctz_type = 'C' AND doc_type = 'Citizen ID card' THEN 1
           WHEN ctz_type = 'N' AND doc_type = 'Non-citizen ID card' THEN 1
           ELSE 0
      END flag 
    FROM citizenships, documents
    WHERE ctz_code = 'TAB'
      AND doc_valid_from (+) < sysdate
      AND doc_valid_to (+) > sysdate
      AND doc_prs_id (+) = ctz_prs_id
  )
)
WHERE rn = 1 AND flag = 0
GROUP BY ctz_code, ctz_type;

Case three: getting the current value and first value in a hierarchy

 

set autotrace off
DROP TABLE addresses;
CREATE TABLE addresses (
   adr_id            NUMBER NOT NULL
  ,adr_adr_id        NUMBER
  ,adr_country_code  VARCHAR2(3) NOT NULL
  ,adr_region_code   VARCHAR2(3)
  ,adr_city_code     VARCHAR2(3)
  ,adr_street        VARCHAR2(10)
);

INSERT ALL
-- one third of all addresses haven't changed over the time
WHEN mod(rn, 3) = 0 THEN
  INTO addresses
  VALUES (rn, NULL, substr(object_type, 1, 3),
          substr(object_type, 4, 3), substr(object_type, 7, 3),
          substr(object_name, 1, 10))
-- one third of all addresses have changed once (street)
WHEN mod(rn, 3) = 1 THEN
  INTO addresses
  VALUES (rn + 200000, rn + 400000, substr(object_type, 1, 3),
          substr(object_type, 4, 3), substr(object_type, 7, 3),
          substr(object_name, 1, 10))
WHEN mod(rn, 3) = 1 THEN
  INTO addresses
  VALUES (rn + 400000, NULL, substr(object_type, 1, 3),
          substr(object_type, 4, 2) || '2',
          substr(object_type, 7, 2) || '2',
          substr(object_name, 3, 10))
-- one third of all addresses have changed twice
WHEN mod(rn, 3) = 2 THEN
  INTO addresses
  VALUES (rn + 600000, rn + 800000, substr(object_type, 1, 3),
          substr(object_type, 4, 3), substr(object_type, 7, 3),
          substr(object_name, 1, 10))
WHEN mod(rn, 3) = 2 THEN
  INTO addresses
  VALUES (rn + 800000, rn + 1000000, substr(object_type, 1, 3),
          substr(object_type, 4, 2) || '2',
          substr(object_type, 7, 2) || '2',
          substr(object_name, 3, 10))
WHEN mod(rn, 3) = 2 THEN
  INTO addresses
  VALUES (rn + 1000000, NULL, substr(object_type, 1, 3),
          substr(object_type, 4, 2) || '3',
          substr(object_type, 7, 2) || '3',
          substr(object_name, 6, 10))
SELECT rownum rn, object_name, object_type FROM all_objects;

-- now just add some constraints and indexes
ALTER TABLE addresses ADD CONSTRAINT adr_pk PRIMARY KEY (adr_id);
ALTER TABLE addresses ADD CONSTRAINT adr_adr_fk
FOREIGN KEY (adr_adr_id) REFERENCES addresses (adr_id);
CREATE INDEX adr_adr_id_idx ON addresses (adr_adr_id);

-- if you are using 9i and query doesn’t return any rows
-- then try using adr_country_code = 'EVA'
-- and adr_region_code = 'LUA'
-- analytic query
SELECT * FROM (
  SELECT last_value(adr_id) OVER (
           PARTITION BY grp ORDER BY rn
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) curr_addr_id,
         last_value(adr_street) OVER (
           PARTITION BY grp ORDER BY rn
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) curr_street,
         lvl, adr_id, adr_country_code, adr_street
  FROM (
    SELECT rn, lvl, adr_id, adr_adr_id,
           adr_country_code, adr_street,
          max(new_rn) OVER (ORDER BY rn) grp
    FROM (
      SELECT rownum rn, level lvl,
             adr_id, adr_adr_id, adr_country_code, adr_street,
             CASE WHEN level = 1 THEN rownum
                  ELSE NULL
             END new_rn
      FROM addresses
      START WITH adr_country_code = 'WIN'
             AND adr_region_code = 'DOW'
      CONNECT BY PRIOR adr_adr_id = adr_id
    )
  )
)
WHERE lvl = 1;

-- another option
SELECT to_number(substr((
         SELECT max(to_char(level, 'fm000009') || ' ' || adr_id)
         FROM addresses adr2
         START WITH adr2.adr_id = adr1.adr_id
         CONNECT BY PRIOR adr_adr_id IS NOT NULL
                AND PRIOR adr_adr_id = adr_id
         ), 8)) curr_addr_id,
       adr_id
FROM addresses adr1
WHERE adr_country_code = 'WIN'
  AND adr_region_code = 'DOW';

Case four: do all that in few sections

set autotrace off
DROP TABLE person_addresses;
DROP TABLE addresses;
DROP TABLE citizenships;
DROP TABLE documents;
DROP TABLE persons;
CREATE TABLE persons (
   prs_id      NUMBER NOT NULL
  ,prs_name    VARCHAR2(15) NOT NULL
  ,prs_surname VARCHAR2(15) NOT NULL
);

CREATE TABLE citizenships (
   ctz_id      NUMBER NOT NULL
  ,ctz
_prs_id  NUMBER NOT NULL

  ,ctz_code    VARCHAR2(3) NOT NULL
  ,ctz_type    VARCHAR2(1) NOT NULL
);

CREATE TABLE addresses (
   adr_id            NUMBER NOT NULL
  ,adr_adr_id        NUMBER
  ,adr_country_code  VARCHAR2(3) NOT NULL
  ,adr_region_code   VARCHAR2(3)
  ,adr_city_code     VARCHAR2(3)
  ,adr_street        VARCHAR2(10)
);

CREATE TABLE person_addresses (
   pra_prs_id            NUMBER NOT NULL
  ,pra
_adr_id            NUMBER NOT NULL

);

INSERT ALL
WHEN 1=1 THEN
  INTO persons (prs_id, prs_name, prs_surname)
  VALUES (rn, substr(object_name,1 , 15), substr(object_name,1 , 15))
-- every person has at least 1 citizenship
-- half of them are citizens
WHEN mod(rn, 2) = 0 THEN
  INTO citizenships
  VALUES (rn, rn, substr(object_type, 1, 3), 'C')
-- half of them are non-citizens
WHEN mod(rn, 2) = 1 THEN
  INTO citizenships
  VALUES (rn, rn, substr(object_type, 1, 3), 'N')
-- several have two
WHEN mod(rn, 5) = 0 AND substr(object_type, 1, 3) <> 'TAB' THEN
  INTO citizenships
  VALUES (rn + 200000, rn, 'TAB', 'C')
-- few have three
WHEN mod(rn, 15) = 0 AND substr(object_type, 1, 3) <> 'SEQ' THEN
  INTO citizenships
  VALUES (rn + 400000, rn, 'SEQ', 'C')
-- just one address for all persons
WHEN 1 = 1 THEN
  INTO addresses
  VALUES (rn, NULL, 'TAB',
          trim(substr(object_type, 2, 3)),
          trim(substr(object_type, 5, 3)),
          trim(substr(object_name, 1, 10)))
WHEN 1 = 1 THEN
  INTO person_addresses
  VALUES (rn, rn)
SELECT rownum rn, object_name, object_type FROM all_objects;

ALTER TABLE persons ADD CONSTRAINT prs_pk PRIMARY KEY (prs_id);
ALTER TABLE person_addresses ADD CONSTRAINT pra_prs_fk
FOREIGN KEY (pra_prs_id) REFERENCES persons (prs_id);
ALTER TABLE addresses ADD CONSTRAINT adr_pk PRIMARY KEY (adr_id);
ALTER TABLE person_addresses ADD CONSTRAINT pra_adr_fk
FOREIGN KEY (pra_adr_id) REFERENCES addresses (adr_id);
CREATE INDEX pra_adr_id_idx ON person_addresses (pra_adr_id);
CREATE INDEX pra_prs_id_idx ON person_addresses (pra_prs_id);

-- country classifier
CREATE TABLE countries AS
SELECT DISTINCT adr_country_code country_code
FROM addresses;
-- region classifier
CREATE TABLE regions AS
SELECT DISTINCT adr_country_code reg_country_code,
     trim(adr_region_code) region_code
FROM addresses
WHERE adr_region_code IS NOT NULL;
--city classifier
CREATE TABLE cities AS
SELECT cit_country_code,
       cit_region_code,
       city_code,
       CASE WHEN mod(rownum, 4) = 0 THEN 1
            ELSE 0
       END cit_is_big
FROM (
  SELECT DISTINCT adr_country_code cit_country_code,
         trim(adr_region_code) cit_region_code,
         trim(adr_city_code) city_code
  FROM addresses
  WHERE adr_city_code IS NOT NULL);
ALTER TABLE countries ADD CONSTRAINT country_pk
PRIMARY KEY (country_code);
ALTER TABLE addresses ADD CONSTRAINT adr_cnt_fk
FOREIGN KEY (adr_country_code) REFERENCES countries (country_code);
ALTER TABLE regions ADD CONSTRAINT regions_pk
PRIMARY KEY (region_code);
ALTER TABLE addresses ADD CONSTRAINT adr_reg_fk
FOREIGN KEY (adr_region_code) REFERENCES regions (region_code);
ALTER TABLE cities ADD CONSTRAINT cities_pk
PRIMARY KEY (city_code);
ALTER TABLE addresses ADD CONSTRAINT adr_cit_fk
FOREIGN KEY (adr_city_code) REFERENCES cities (city_code);

-- select used in WITH clause
SELECT count(*) cnt,
       ctz_code,
       ctz_type,
       adr_country_code cnt,
       adr_region_code  reg,
       adr_city_code    cty
FROM addresses, person_addresses,
-- a bit modified select from case1, just without grouping
  (SELECT * FROM (
    SELECT ctz_prs_id, ctz_code, ctz_type,
      CASE WHEN ctz_prs_id <> prev_prs_id THEN 1
           WHEN first_code = 'TAB' THEN 0
           ELSE 1
      END flag
    FROM (
      SELECT
        ctz_prs_id,
        ctz_code,
        ctz_type,
        first_value (ctz_code) OVER (PARTITION BY ctz_prs_id
            ORDER BY decode (ctz_code, 'TAB', 0, 1)) first_code,
        lag(ctz_prs_id, 1, -1) OVER (ORDER BY ctz_prs_id,
                     decode (ctz_code, 'TAB', 0, 1)) prev_prs_id
      FROM citizenships
    )
  )
  WHERE flag = 1) ctz_source
WHERE ctz_source.ctz_prs_id = pra_prs_id
  AND pra_adr_id = adr_id
GROUP BY ctz_code,
         ctz_type,
         adr_country_code,
         adr_region_code,
         adr_city_code;

-- part of result follows

     CNT CTZ C CNT REG CTY

-------- --- - --- --- ---

       1 CON C TAB ONS UME

       1 CON N TAB ONS UME

       1 DIR C TAB IRE CTO

       1 DIR N TAB IRE CTO

       3 EVA C TAB VAL UAT

       5 EVA N TAB VAL UAT

     274 FUN C TAB UNC TIO

     281 FUN N TAB UNC TIO

    2392 IND C TAB NDE X

      61 IND C TAB NDE X P

       2 IND C TAB NDE XTY

 

-- complete query
BREAK ON country
COLUMN tag NOPRINT
WITH subsel AS (
SELECT count(*) cnt,
       ctz_code,
       ctz_type,
       adr_country_code ctr,
       adr_region_code  reg,
       adr_city_code    cty
FROM addresses, person_addresses,
-- a bit modified select from case1, just without grouping
  (SELECT * FROM (
    SELECT ctz_prs_id, ctz_code, ctz_type,
      CASE WHEN ctz_prs_id <> prev_prs_id THEN 1
           WHEN first_code = 'TAB' THEN 0
           ELSE 1
      END flag
    FROM (
      SELECT
        ctz_prs_id,
        ctz_code,
        ctz_type,
        first_value (ctz_code) OVER (PARTITION BY ctz_prs_id
            ORDER BY decode (ctz_code, 'TAB', 0, 1)) first_code,
        lag(ctz_prs_id, 1, -1) OVER (ORDER BY ctz_prs_id,
                     decode (ctz_code, 'TAB', 0, 1)) prev_prs_id
      FROM citizenships
    )
  )
  WHERE flag = 1) ctz_source
WHERE ctz_source.ctz_prs_id = pra_prs_id
  AND pra_adr_id = adr_id
GROUP BY ctz_code,
         ctz_type,
         adr_country_code,
         adr_region_code,
         adr_city_code
)
SELECT tag, country, region, city, sum_of_TAB_citizens, sum_of_TAB_noncitizens, 
       sum_of_oth_citizens, sum_of_oth_noncitizens
FROM (
-- all teritories
SELECT 0 tag,
       NULL country,
       'all countries' region,
       'all countries' city,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_noncitizens,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_noncitizens
FROM subsel
UNION ALL
-- all countries
SELECT 1 tag,
       ctr country,
       'countries' region,
       'countries' city,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_noncitizens,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_noncitizens
FROM subsel
GROUP BY ctr
UNION ALL
-- all countries with big cities
SELECT 2 tag,
       ctr country,
       'big_cities' region,
       cty city,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_noncitizens,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_noncitizens
FROM subsel, cities
WHERE subsel.ctr = cit_country_code
  AND subsel.cty = city_code
  AND cit_is_big = 1
GROUP BY ctr, cty
UNION ALL
-- all countries with regions
SELECT 3 tag,
       ctr country,
       reg region,
       'regions' city,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_noncitizens,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_noncitizens
FROM subsel, regions
WHERE subsel.ctr = reg_country_code
  AND subsel.reg = region_code
GROUP BY ctr, reg
UNION ALL
-- all countries with regions with cities
SELECT 4 tag,
       ctr country,
       reg region,
       cty city,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code = 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_TAB_noncitizens,
       nvl(SUM(CASE WHEN ctz_type = 'C' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_citizens,
       nvl(SUM(CASE WHEN ctz_type = 'N' AND ctz_code <> 'TAB'
               THEN cnt
               ELSE 0 END), 0) sum_of_oth_noncitizens
FROM subsel, cities
WHERE subsel.ctr = cit_country_code
  AND subsel.reg = cit_region_code
  AND subsel.cty = city_code
GROUP BY ctr, reg, cty
)
ORDER BY country NULLS FIRST, decode(region, 'countries', '0', 'big_cities', '1', region),
         decode(city, 'regions', '0', city), tag;

-- part of result follows

COU REGION        CITY          SUM_OF_TAB_CITIZENS SUM_OF_TAB_NONCITIZENS SUM_OF_OTH_CITIZENS SUM_OF_OTH_NONCITIZENS

--- ------------- ------------- ------------------- ---------------------- ------------------- ----------------------

    all countries all countries               13273                   2267               22185                  22150

TAB countries     countries                   13273                   2267               22185                  22150

    big_cities    BO                             14                      0                  19                     21

    big_cities    EXT                             2                      0                   0                      0

    big_cities    RIA                             0                      0                   4                      2

    big_cities    SE                              4                      0                   6                      5

    big_cities    SO                              4                      0                   6                      6

    big_cities    X                            1182                      0                2392                   2379

    ABL           regions                      2195                   2267                   0                      0

    ABL           E                            2162                   2209                   0                      0

    ABL           E P                            33                     58                   0                      0

    ACK           regions                      1606                      0                3173                   3180

    ACK           AGE                          1606                      0                3173                   3180

    ATE           regions                         0                      0                   4                      2

    ATE           RIA                             0                      0                   4                      2

    AVA           regions                      2154                      0                2151                   6444

    AVA           CL                           2053                      0                1953                   6243

    AVA           DA                             58                      0                 119                    116

    AVA           RE                             39                      0                  73                     79

    AVA           SO                              4                      0                   6                      6

    EQU           regions                       204                      0                 420                    410

    EQU           ENC                           204                      0                 420                    410

    IBR           regions                        18                      0                  35                     37

    IBR           ARY                            18                      0                  35                     37

    IEW           regions                       704                      0                1483                   1296

    IRE           regions                         1                      0                   1                      1

    IRE           CTO                             1                      0                   1                      1

    YNO           regions                      3608                      0                9303                   5160

    YNO           NYM                          3608                      0                9303                   5160

About author

Gints Plivna gints.plivna@gmail.com is system analyst in Rix Technologies Ltd. (www.rixtech.lv). He has experience in working with Oracle since 1997 and his interests mostly have been connected with analyzing system requirements, design, development and SQL tuning.

 

Contacts:

e-mail - gints.plivna@gmail.com

website - http://www.gplivna.eu/

Licenses

 

This work is licensed under the Creative Commons Attribution-ShareAlike 2.5 License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/2.5/ or send a letter to Creative Commons, 543 Howard Street, 5th Floor, San Francisco, California, 94105, USA.