|
|
| (Una versione intermedia di uno stesso utente non è mostrata) |
| Riga 1: |
Riga 1: |
| − | = SCRIPT SQL PER PAPER =
| + | [[File:logo-paper.jpg]] |
| − | | |
| − | Questa e' una raccolta di SCRIPT SQL utili per visualizzare/associare/comparare i dati delle tabelle del database di PAPER.
| |
| − | | |
| − | == Albero delle unita' organizzative ==
| |
| − | | |
| − | <code sql>
| |
| − | WITH UO (UOID, UOID_PARENT, LIVELLO) AS (
| |
| − | SELECT u1.TBUO_ID, NULL, 0 AS LIVELLO
| |
| − | FROM [PAPER].[00000].[TBSR_UNITAORG] u1
| |
| − | LEFT OUTER JOIN [PAPER].[00000].[TBSR_SUBUNITAORG] sub1 ON sub1.TBUO_ID = u1.TBUO_ID
| |
| − | WHERE TBUO_PARENTID IS NULL
| |
| − | UNION ALL
| |
| − | SELECT TBUO_ID, TBUO_PARENTID, 1 AS LIVELLO
| |
| − | FROM [PAPER].[00000].[TBSR_SUBUNITAORG]
| |
| − | UNION ALL
| |
| − | SELECT UO.UOID, sub.TBUO_PARENTID, LIVELLO + 1
| |
| − | FROM UO, [PAPER].[00000].[TBSR_SUBUNITAORG] sub
| |
| − | WHERE UO.UOID_PARENT = sub.TBUO_ID
| |
| − | )
| |
| − | SELECT LIVELLO, UOID_PARENT, u2.TBUO_UNITAORG, UOID, u1.TBUO_UNITAORG
| |
| − | FROM UO
| |
| − | LEFT OUTER JOIN [PAPER].[00000].[TBSR_UNITAORG] u1 on u1.TBUO_ID = UOID
| |
| − | LEFT OUTER JOIN [PAPER].[00000].[TBSR_UNITAORG] u2 on u2.TBUO_ID = UOID_PARENT
| |
| − | ORDER BY UOID_PARENT, LIVELLO, UOID
| |
| − | </code>
| |
| − | | |
| − | == RUOLI - PERMESSI ==
| |
| − | | |
| − | <code sql>
| |
| − | | |
| − | /* === RUOLI - PERMESSI ================================================================= */
| |
| − | | |
| − | SELECT [TBSR_RUOLI].[TBRL_ID],[TBSR_RUOLI].[TBRL_RUOLO],[TBSR_PERMESSI].[TBPR_CODTRX]
| |
| − | FROM
| |
| − | [PAPER].[00000].[TBSR_RUOLI] LEFT JOIN [PAPER].[00000].[TBSR_PERMESSI]
| |
| − | ON [PAPER].[00000].[TBSR_RUOLI].[TBRL_ID] = [PAPER].[00000].[TBSR_PERMESSI].[TBRL_ID]
| |
| − | ORDER BY [TBSR_RUOLI].[TBRL_RUOLO]
| |
| − | GO
| |
| − | | |
| − | /* === CODTRX - RUOLI =================================================================== */
| |
| − | | |
| − | SELECT [TBSR_CODTRX].[TBPR_CODTRX],[TBSR_RUOLI].[TBRL_RUOLO],[TBSR_RUOLI].[TBRL_ID]
| |
| − | FROM
| |
| − | ([PAPER].[00000].[TBSR_CODTRX] LEFT JOIN [PAPER].[00000].[TBSR_PERMESSI]
| |
| − | ON [TBSR_CODTRX].[TBPR_CODTRX] = [TBSR_PERMESSI].TBPR_CODTRX)
| |
| − | LEFT JOIN [PAPER].[00000].[TBSR_RUOLI]
| |
| − | ON [TBSR_PERMESSI].TBRL_ID = [TBSR_RUOLI].TBRL_ID
| |
| − | GO
| |
| − | | |
| − | </code>
| |
| − | | |
| − | == UTENTI - UNITA ==
| |
| − | | |
| − | <code sql>
| |
| − | | |
| − | /* === UTENTI - UNITA ========================================================= */
| |
| − | | |
| − | SELECT [TBSR_UTENTI].[TBUT_ID], [TBUT_COGNOME], [TBUT_NOME], [TBUO_UNITAORG], [TBSR_UNITAORG].[TBUO_ID]
| |
| − | FROM
| |
| − | ([PAPER].[00000].[TBSR_UTENTI] LEFT JOIN [PAPER].[00000].[TBSR_UNITAORG_UTENTI]
| |
| − | ON [TBSR_UTENTI].[TBUT_ID] = [TBSR_UNITAORG_UTENTI].[TBUT_ID])
| |
| − | LEFT JOIN [PAPER].[00000].[TBSR_UNITAORG]
| |
| − | ON [TBSR_UNITAORG_UTENTI].[TBUO_ID] = [TBSR_UNITAORG].[TBUO_ID]
| |
| − | ORDER BY [TBUT_COGNOME], [TBUT_NOME]
| |
| − | GO
| |
| − | | |
| − | /* === UNITA - UTENTI ========================================================= */
| |
| − | | |
| − | SELECT [TBSR_UNITAORG].[TBUO_ID],[TBSR_UNITAORG].[TBUO_UNITAORG],[TBSR_UTENTI].[TBUT_COGNOME],[TBSR_UTENTI].[TBUT_NOME], [TBSR_UTENTI].[TBUT_ID]
| |
| − | FROM
| |
| − | ([PAPER].[00000].[TBSR_UNITAORG] LEFT JOIN [PAPER].[00000].[TBSR_UNITAORG_UTENTI]
| |
| − | ON [PAPER].[00000].[TBSR_UNITAORG].[TBUO_ID] = [PAPER].[00000].[TBSR_UNITAORG_UTENTI].[TBUO_ID])
| |
| − | LEFT JOIN [PAPER].[00000].[TBSR_UTENTI]
| |
| − | ON [PAPER].[00000].[TBSR_UNITAORG_UTENTI].[TBUT_ID] = [PAPER].[00000].[TBSR_UTENTI].[TBUT_ID]
| |
| − | ORDER BY [TBSR_UNITAORG].[TBUO_UNITAORG]
| |
| − | GO
| |
| − | | |
| − | </code>
| |
| − | | |
| − | == UTENTI - RUOLI ==
| |
| − | | |
| − | <code sql>
| |
| − | | |
| − | /* === UTENTI -> RUOLI ================================================== */
| |
| − | | |
| − | SELECT [TBSR_UTENTI].[TBUT_ID], [TBUT_COGNOME], [TBUT_NOME], [TBRL_RUOLO], [TBSR_RUOLI].[TBRL_ID]
| |
| − | FROM
| |
| − | ([PAPER].[00000].[TBSR_UTENTI] LEFT JOIN [PAPER].[00000].[TBSR_RUOLIUTENTI]
| |
| − | ON [TBSR_UTENTI].[TBUT_ID] = [TBSR_RUOLIUTENTI].[TBUT_ID])
| |
| − | LEFT JOIN [PAPER].[00000].[TBSR_RUOLI]
| |
| − | ON [TBSR_RUOLIUTENTI].[TBRL_ID] = [TBSR_RUOLI].[TBRL_ID]
| |
| − | ORDER BY [TBUT_COGNOME], [TBUT_NOME]
| |
| − | GO
| |
| − | | |
| − | /* === RUOLI -> UTENTI ================================================== */
| |
| − | | |
| − | SELECT [TBSR_RUOLI].[TBRL_ID], [TBRL_RUOLO], [TBUT_COGNOME], [TBUT_NOME], [TBSR_UTENTI].[TBUT_ID]
| |
| − | FROM
| |
| − | | |
| − | ([PAPER].[00000].[TBSR_RUOLI] LEFT JOIN [PAPER].[00000].[TBSR_RUOLIUTENTI]
| |
| − | ON [TBSR_RUOLI].[TBRL_ID] = [TBSR_RUOLIUTENTI].[TBRL_ID])
| |
| − | LEFT JOIN [PAPER].[00000].[TBSR_UTENTI]
| |
| − | ON [TBSR_RUOLIUTENTI].[TBUT_ID] = [TBSR_UTENTI].[TBUT_ID]
| |
| − | | |
| − | ORDER BY [TBRL_RUOLO], [TBUT_COGNOME], [TBUT_NOME]
| |
| − | GO
| |
| − | | |
| − | </code>
| |
| − | | |
| − | == ISTITUTI ed UO ASSOCIATA ==
| |
| − | | |
| − | <code sql>
| |
| − | | |
| − | SELECT [TBSR_ISTITUTI].[TBIS_ABI],[TBSR_ISTITUTI].[TBIS_ISTITUTO],[TBSR_ISTITUTI_UNITAORG].[TBUO_ID],[TBSR_UNITAORG].[TBUO_UNITAORG]
| |
| − | FROM
| |
| − |
| |
| − | ([PAPER].[00000].[TBSR_ISTITUTI] LEFT JOIN [PAPER].[00000].[TBSR_ISTITUTI_UNITAORG]
| |
| − | ON [TBSR_ISTITUTI].[TBIS_ABI] = [TBSR_ISTITUTI_UNITAORG].[TBIS_ABI])
| |
| − | LEFT JOIN [PAPER].[00000].[TBSR_UNITAORG]
| |
| − | ON [TBSR_ISTITUTI_UNITAORG].[TBUO_ID] = [TBSR_UNITAORG].[TBUO_ID]
| |
| − | ORDER BY [TBSR_ISTITUTI].[TBIS_ABI]
| |
| − | GO
| |
| − | | |
| − | </code>
| |
