Klauzula MODEL - najprostsze przykłady

Klauzulę MODEL umieszczamy w zapytaniu  przed klauzulą porządkującą ORDER BY. Służy ona do wykonywania dość złożonych obliczeń  na jedno i wielowymiarowych arkuszach, jest to pewna proteza MS Excel w SQL. Poniżej na przykładzie powszechnie znanej tabeli słownikowej zobaczymy jej działanie
SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
 DIMENSION BY (TABLE_NAME)
 MEASURES( NUM_ROWS)
 RULES();

Zapytanie zwraca nam tylko kolumny zdefiniowane w klauzulach wymiarów( DIMENSION BY) i miar ( MEASURES ). Wymiar to zbiór kolumn o niepustych wartościach, który pełni funkcję  klucza, przez co zawsze dla zapytania musi być unikalny. Akurat nazwa tabeli w danym schemacie musi być unikalna. Miara to dowolna kolumna z zapytania lub zdefiniowana przez użytkownika.
Zatem jako wynik otrzymamy  kolumny TABLE_NAME i NUM_ROWS z widoku ALL_TABLES.

Poniższe zapytanie zwróci błąd ORA-32638: Non unique addressing in MODEL dimensions, bo dla każdej nazwy tabeli, która ma być kluczem, występują w wyniku operacji UNION ALL na dwóch identycznych zbiorach, Jako wymiaru można użyć funkcji ROWNUM, ale czy takie rozwiązanie ma sens ??
SELECT* FROM
( 
       SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
           UNION ALL
      SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
)
MODEL
 DIMENSION BY (TABLE_NAME )
 MEASURES( NUM_ROWS )
 RULES();


Dla wymiarów i miar można stosować  aliasy, co pokazuje poniższy przykład - zapytanie zwróci nam wiersze NAZWA_TABELI i LICZBA_WIERSZY .

SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
DIMENSION BY (TABLE_NAME AS NAZWA_TABELI)
 MEASURES( NUM_ROWS AS LICZBA_WIERSZY)
 RULES(); 

Jak należy definiować własne kolumny?? Poniższy przykład pokazuje kilka możliwości:

 SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
DIMENSION BY (TABLE_NAME AS NAZWA_TABELI)
 MEASURES( 
       NUM_ROWS AS LICZBA_WIERSZY,
      'NIC' AS NUM_ROWS,
       TRUNC(SYSDATE) AS DATA,
       CAST(NULL AS VARCHAR2(30 CHAR) ) AS TEKST,
      1 LICZBA)
 RULES(); 

 Następujące kolumny miar zostały utworzone:

1. LICZBA_WIERSZY  -  to kolumna NUM_ROWS z widoku
2. NUM_ROWS -  to kolumna tekstowa zainicjowana słowem NIC, czyli może mieć długość maksymalnie trzy znaki
3. DATA - kolumna typu DATE zainicjowana bieżącą datą
4. TEKST - trzydziestoznakowy, niezainicjowana kolumna typu VARCHAR2
5. LICZBA - kolumna typu NUMBER