Visão geral:
Comando que retorna os dados de indicadores, usuários e suas permissões nos indicadores.
Tabelas relacionadas
TABELA
|
Descrição
|
Chave primária (PK)
|
Tabelas de ligação
|
STSCORECARDTREE
|
Armazena a estrutura do scorecard
|
CDSCORECARDTREE
CDSCORECARD
CDREVISION
|
STSCORECARD
STSCSTRUCTITEM
STSCMETRIC
|
STSCORECARD
|
Armazena os scorecards
|
CDSCORECARD
CDREVISION
|
STSCORECARDTREE
STSCSTRUCTITEM
STSCMETRIC
|
STSCSTRUCTITEM
|
Armazena os elementos do scorecard
|
CDSCSTRUCTITEM
CDSCORECARD
CDREVISION
|
STSCOREITEM
STSCORECARD
STSCORECARDTREE
|
STSCMETRIC
|
Armazena os indicadores do scorecard
|
CDSCMETRIC
CDSCORECARD
CDREVISION
|
STMETRIC
STSCORECARD
STSCORECARDTREE
|
STSCOREITEM
|
Armazena os elementos
|
CDSCOREITEM
|
STSCSTRUCTITEM
|
STMETRIC
|
Armazena os indicadores
|
CDMETRIC
|
STSCMETRIC
|
Oracle
SELECT
CASE
WHEN OWNER.FGNODETYPE = 0 /* OBS 1 */
THEN SC_OWNER.IDSCORECARD || ' - ' || SC_OWNER.NMSCORECARD
WHEN OWNER.FGNODETYPE = 1 /* OBS 2 */
THEN SC_ELEMENT_OWNER.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER.NMSCOREITEM
WHEN OWNER.FGNODETYPE = 2 /* OBS 3 */
THEN SC_METRIC_OWNER.IDSCMETRIC || ' - ' || METRIC_OWNER.NMMETRIC
END AS N1,
CASE
WHEN OWNER_2.FGNODETYPE = 0
THEN SC_OWNER_2.IDSCORECARD || ' - ' || SC_OWNER_2.NMSCORECARD
WHEN OWNER_2.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_2.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER_2.NMSCOREITEM
WHEN OWNER_2.FGNODETYPE = 2
THEN SC_METRIC_OWNER_2.IDSCMETRIC || ' - ' || METRIC_OWNER_2.NMMETRIC
END AS N2,
CASE
WHEN OWNER_3.FGNODETYPE = 0
THEN SC_OWNER_3.IDSCORECARD || ' - ' || SC_OWNER_3.NMSCORECARD
WHEN OWNER_3.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_3.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER_3.NMSCOREITEM
WHEN OWNER_3.FGNODETYPE = 2
THEN SC_METRIC_OWNER_3.IDSCMETRIC || ' - ' || METRIC_OWNER_3.NMMETRIC
END AS N3,
CASE
WHEN OWNER_4.FGNODETYPE = 0
THEN SC_OWNER_4.IDSCORECARD || ' - ' || SC_OWNER_4.NMSCORECARD
WHEN OWNER_4.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_4.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER_4.NMSCOREITEM
WHEN OWNER_4.FGNODETYPE = 2
THEN SC_METRIC_OWNER_4.IDSCMETRIC || ' - ' || METRIC_OWNER_4.NMMETRIC
END AS N4,
CASE
WHEN OWNER_5.FGNODETYPE = 0
THEN SC_OWNER_5.IDSCORECARD || ' - ' || SC_OWNER_5.NMSCORECARD
WHEN OWNER_5.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_5.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER_5.NMSCOREITEM
WHEN OWNER_5.FGNODETYPE = 2
THEN SC_METRIC_OWNER_5.IDSCMETRIC || ' - ' || METRIC_OWNER_5.NMMETRIC
END AS N5
FROM STSCORECARDTREE
INNER JOIN STSCORECARD ON(STSCORECARD.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCORECARD.CDREVISION = STSCORECARDTREE.CDREVISION AND STSCORECARD.FGCURRENT = 1) /* OBS 4 */
INNER JOIN STSCORECARDTREE OWNER ON(OWNER.CDSCORECARDTREE = STSCORECARDTREE.CDSCORECARDTREEOWNER) /* OBS 5 */
LEFT OUTER JOIN STSCSTRUCTITEM ON(STSCSTRUCTITEM.CDSCSTRUCTITEM = STSCORECARDTREE.CDSCSTRUCTITEM AND STSCSTRUCTITEM.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCSTRUCTITEM.CDREVISION = STSCORECARDTREE.CDREVISION)
LEFT OUTER JOIN STSCMETRIC ON(STSCMETRIC.CDSCMETRIC = STSCORECARDTREE.CDSCMETRIC AND STSCMETRIC.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCMETRIC.CDREVISION = STSCORECARDTREE.CDREVISION)
--N1
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER ON(SC_ELEMENT_OWNER.CDSCSTRUCTITEM = OWNER.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_ELEMENT_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 1) /* OBS 6 */
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER ON(ELEMENT_OWNER.CDSCOREITEM = SC_ELEMENT_OWNER.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER ON(SC_METRIC_OWNER.CDSCMETRIC = OWNER.CDSCMETRIC AND SC_METRIC_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_METRIC_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 2) /* OBS 7 */
LEFT OUTER JOIN STMETRIC METRIC_OWNER ON(METRIC_OWNER.CDMETRIC = SC_METRIC_OWNER.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER ON(SC_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 0) /* OBS 8 */
--N2
LEFT OUTER JOIN STSCORECARDTREE OWNER_2 ON(OWNER_2.CDSCORECARDTREE = OWNER.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_2 ON(SC_ELEMENT_OWNER_2.CDSCSTRUCTITEM = OWNER_2.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_ELEMENT_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_2 ON(ELEMENT_OWNER_2.CDSCOREITEM = SC_ELEMENT_OWNER_2.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_2 ON(SC_METRIC_OWNER_2.CDSCMETRIC = OWNER_2.CDSCMETRIC AND SC_METRIC_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_METRIC_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_2 ON(METRIC_OWNER_2.CDMETRIC = SC_METRIC_OWNER_2.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_2 ON(SC_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 0)
--N3
LEFT OUTER JOIN STSCORECARDTREE OWNER_3 ON(OWNER_3.CDSCORECARDTREE = OWNER_2.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_3 ON(SC_ELEMENT_OWNER_3.CDSCSTRUCTITEM = OWNER_3.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_ELEMENT_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_3 ON(ELEMENT_OWNER_3.CDSCOREITEM = SC_ELEMENT_OWNER_3.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_3 ON(SC_METRIC_OWNER_3.CDSCMETRIC = OWNER_3.CDSCMETRIC AND SC_METRIC_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_METRIC_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_3 ON(METRIC_OWNER_3.CDMETRIC = SC_METRIC_OWNER_3.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_3 ON(SC_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 0)
--N4
LEFT OUTER JOIN STSCORECARDTREE OWNER_4 ON(OWNER_4.CDSCORECARDTREE = OWNER_3.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_4 ON(SC_ELEMENT_OWNER_4.CDSCSTRUCTITEM = OWNER_4.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_ELEMENT_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_4 ON(ELEMENT_OWNER_4.CDSCOREITEM = SC_ELEMENT_OWNER_4.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_4 ON(SC_METRIC_OWNER_4.CDSCMETRIC = OWNER_4.CDSCMETRIC AND SC_METRIC_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_METRIC_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_4 ON(METRIC_OWNER_4.CDMETRIC = SC_METRIC_OWNER_4.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_4 ON(SC_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 0)
--N5
LEFT OUTER JOIN STSCORECARDTREE OWNER_5 ON(OWNER_5.CDSCORECARDTREE = OWNER_4.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_5 ON(SC_ELEMENT_OWNER_5.CDSCSTRUCTITEM = OWNER_5.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_ELEMENT_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_5 ON(ELEMENT_OWNER_5.CDSCOREITEM = SC_ELEMENT_OWNER_5.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_5 ON(SC_METRIC_OWNER_5.CDSCMETRIC = OWNER_5.CDSCMETRIC AND SC_METRIC_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_METRIC_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_5 ON(METRIC_OWNER_5.CDMETRIC = SC_METRIC_OWNER_5.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_5 ON(SC_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 0)
WHERE STSCMETRIC.IDSCMETRIC = 'Identificador do indicador do Scorecard' /* OBS 9 */
OR STSCSTRUCTITEM.IDSCSTRUCTITEM = 'Identificador do elemento do Scorecard' /* OBS 10 */
|
Postgres
SELECT
CASE
WHEN OWNER.FGNODETYPE = 0 /* OBS 1 */
THEN SC_OWNER.IDSCORECARD || ' - ' || SC_OWNER.NMSCORECARD
WHEN OWNER.FGNODETYPE = 1 /* OBS 2 */
THEN SC_ELEMENT_OWNER.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER.NMSCOREITEM
WHEN OWNER.FGNODETYPE = 2 /* OBS 3 */
THEN SC_METRIC_OWNER.IDSCMETRIC || ' - ' || METRIC_OWNER.NMMETRIC
END AS N1,
CASE
WHEN OWNER_2.FGNODETYPE = 0
THEN SC_OWNER_2.IDSCORECARD || ' - ' || SC_OWNER_2.NMSCORECARD
WHEN OWNER_2.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_2.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER_2.NMSCOREITEM
WHEN OWNER_2.FGNODETYPE = 2
THEN SC_METRIC_OWNER_2.IDSCMETRIC || ' - ' || METRIC_OWNER_2.NMMETRIC
END AS N2,
CASE
WHEN OWNER_3.FGNODETYPE = 0
THEN SC_OWNER_3.IDSCORECARD || ' - ' || SC_OWNER_3.NMSCORECARD
WHEN OWNER_3.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_3.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER_3.NMSCOREITEM
WHEN OWNER_3.FGNODETYPE = 2
THEN SC_METRIC_OWNER_3.IDSCMETRIC || ' - ' || METRIC_OWNER_3.NMMETRIC
END AS N3,
CASE
WHEN OWNER_4.FGNODETYPE = 0
THEN SC_OWNER_4.IDSCORECARD || ' - ' || SC_OWNER_4.NMSCORECARD
WHEN OWNER_4.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_4.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER_4.NMSCOREITEM
WHEN OWNER_4.FGNODETYPE = 2
THEN SC_METRIC_OWNER_4.IDSCMETRIC || ' - ' || METRIC_OWNER_4.NMMETRIC
END AS N4,
CASE
WHEN OWNER_5.FGNODETYPE = 0
THEN SC_OWNER_5.IDSCORECARD || ' - ' || SC_OWNER_5.NMSCORECARD
WHEN OWNER_5.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_5.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER_5.NMSCOREITEM
WHEN OWNER_5.FGNODETYPE = 2
THEN SC_METRIC_OWNER_5.IDSCMETRIC || ' - ' || METRIC_OWNER_5.NMMETRIC
END AS N5
FROM STSCORECARDTREE
INNER JOIN STSCORECARD ON(STSCORECARD.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCORECARD.CDREVISION = STSCORECARDTREE.CDREVISION AND STSCORECARD.FGCURRENT = 1) /* OBS 4 */
INNER JOIN STSCORECARDTREE OWNER ON(OWNER.CDSCORECARDTREE = STSCORECARDTREE.CDSCORECARDTREEOWNER) /* OBS 5 */
LEFT OUTER JOIN STSCSTRUCTITEM ON(STSCSTRUCTITEM.CDSCSTRUCTITEM = STSCORECARDTREE.CDSCSTRUCTITEM AND STSCSTRUCTITEM.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCSTRUCTITEM.CDREVISION = STSCORECARDTREE.CDREVISION)
LEFT OUTER JOIN STSCMETRIC ON(STSCMETRIC.CDSCMETRIC = STSCORECARDTREE.CDSCMETRIC AND STSCMETRIC.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCMETRIC.CDREVISION = STSCORECARDTREE.CDREVISION)
--N1
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER ON(SC_ELEMENT_OWNER.CDSCSTRUCTITEM = OWNER.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_ELEMENT_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 1) /* OBS 6 */
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER ON(ELEMENT_OWNER.CDSCOREITEM = SC_ELEMENT_OWNER.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER ON(SC_METRIC_OWNER.CDSCMETRIC = OWNER.CDSCMETRIC AND SC_METRIC_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_METRIC_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 2) /* OBS 7 */
LEFT OUTER JOIN STMETRIC METRIC_OWNER ON(METRIC_OWNER.CDMETRIC = SC_METRIC_OWNER.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER ON(SC_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 0) /* OBS 8 */
--N2
LEFT OUTER JOIN STSCORECARDTREE OWNER_2 ON(OWNER_2.CDSCORECARDTREE = OWNER.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_2 ON(SC_ELEMENT_OWNER_2.CDSCSTRUCTITEM = OWNER_2.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_ELEMENT_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_2 ON(ELEMENT_OWNER_2.CDSCOREITEM = SC_ELEMENT_OWNER_2.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_2 ON(SC_METRIC_OWNER_2.CDSCMETRIC = OWNER_2.CDSCMETRIC AND SC_METRIC_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_METRIC_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_2 ON(METRIC_OWNER_2.CDMETRIC = SC_METRIC_OWNER_2.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_2 ON(SC_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 0)
--N3
LEFT OUTER JOIN STSCORECARDTREE OWNER_3 ON(OWNER_3.CDSCORECARDTREE = OWNER_2.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_3 ON(SC_ELEMENT_OWNER_3.CDSCSTRUCTITEM = OWNER_3.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_ELEMENT_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_3 ON(ELEMENT_OWNER_3.CDSCOREITEM = SC_ELEMENT_OWNER_3.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_3 ON(SC_METRIC_OWNER_3.CDSCMETRIC = OWNER_3.CDSCMETRIC AND SC_METRIC_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_METRIC_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_3 ON(METRIC_OWNER_3.CDMETRIC = SC_METRIC_OWNER_3.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_3 ON(SC_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 0)
--N4
LEFT OUTER JOIN STSCORECARDTREE OWNER_4 ON(OWNER_4.CDSCORECARDTREE = OWNER_3.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_4 ON(SC_ELEMENT_OWNER_4.CDSCSTRUCTITEM = OWNER_4.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_ELEMENT_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_4 ON(ELEMENT_OWNER_4.CDSCOREITEM = SC_ELEMENT_OWNER_4.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_4 ON(SC_METRIC_OWNER_4.CDSCMETRIC = OWNER_4.CDSCMETRIC AND SC_METRIC_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_METRIC_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_4 ON(METRIC_OWNER_4.CDMETRIC = SC_METRIC_OWNER_4.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_4 ON(SC_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 0)
--N5
LEFT OUTER JOIN STSCORECARDTREE OWNER_5 ON(OWNER_5.CDSCORECARDTREE = OWNER_4.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_5 ON(SC_ELEMENT_OWNER_5.CDSCSTRUCTITEM = OWNER_5.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_ELEMENT_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_5 ON(ELEMENT_OWNER_5.CDSCOREITEM = SC_ELEMENT_OWNER_5.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_5 ON(SC_METRIC_OWNER_5.CDSCMETRIC = OWNER_5.CDSCMETRIC AND SC_METRIC_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_METRIC_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_5 ON(METRIC_OWNER_5.CDMETRIC = SC_METRIC_OWNER_5.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_5 ON(SC_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 0)
WHERE STSCMETRIC.IDSCMETRIC = 'Identificador do indicador do Scorecard' /* OBS 9 */
OR STSCSTRUCTITEM.IDSCSTRUCTITEM = 'Identificador do elemento do Scorecard' /* OBS 10 */
|
SQL Server
SELECT
CASE
WHEN OWNER.FGNODETYPE = 0 /* OBS 1 */
THEN SC_OWNER.IDSCORECARD + ' - ' + SC_OWNER.NMSCORECARD
WHEN OWNER.FGNODETYPE = 1 /* OBS 2 */
THEN SC_ELEMENT_OWNER.IDSCSTRUCTITEM + ' - ' + ELEMENT_OWNER.NMSCOREITEM
WHEN OWNER.FGNODETYPE = 2 /* OBS 3 */
THEN SC_METRIC_OWNER.IDSCMETRIC + ' - ' + METRIC_OWNER.NMMETRIC
END AS N1,
CASE
WHEN OWNER_2.FGNODETYPE = 0
THEN SC_OWNER_2.IDSCORECARD + ' - ' + SC_OWNER_2.NMSCORECARD
WHEN OWNER_2.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_2.IDSCSTRUCTITEM + ' - ' + ELEMENT_OWNER_2.NMSCOREITEM
WHEN OWNER_2.FGNODETYPE = 2
THEN SC_METRIC_OWNER_2.IDSCMETRIC + ' - ' + METRIC_OWNER_2.NMMETRIC
END AS N2,
CASE
WHEN OWNER_3.FGNODETYPE = 0
THEN SC_OWNER_3.IDSCORECARD + ' - ' + SC_OWNER_3.NMSCORECARD
WHEN OWNER_3.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_3.IDSCSTRUCTITEM + ' - ' + ELEMENT_OWNER_3.NMSCOREITEM
WHEN OWNER_3.FGNODETYPE = 2
THEN SC_METRIC_OWNER_3.IDSCMETRIC + ' - ' + METRIC_OWNER_3.NMMETRIC
END AS N3,
CASE
WHEN OWNER_4.FGNODETYPE = 0
THEN SC_OWNER_4.IDSCORECARD + ' - ' + SC_OWNER_4.NMSCORECARD
WHEN OWNER_4.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_4.IDSCSTRUCTITEM + ' - ' + ELEMENT_OWNER_4.NMSCOREITEM
WHEN OWNER_4.FGNODETYPE = 2
THEN SC_METRIC_OWNER_4.IDSCMETRIC + ' - ' + METRIC_OWNER_4.NMMETRIC
END AS N4,
CASE
WHEN OWNER_5.FGNODETYPE = 0
THEN SC_OWNER_5.IDSCORECARD + ' - ' + SC_OWNER_5.NMSCORECARD
WHEN OWNER_5.FGNODETYPE = 1
THEN SC_ELEMENT_OWNER_5.IDSCSTRUCTITEM + ' - ' + ELEMENT_OWNER_5.NMSCOREITEM
WHEN OWNER_5.FGNODETYPE = 2
THEN SC_METRIC_OWNER_5.IDSCMETRIC + ' - ' + METRIC_OWNER_5.NMMETRIC
END AS N5
FROM STSCORECARDTREE
INNER JOIN STSCORECARD ON(STSCORECARD.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCORECARD.CDREVISION = STSCORECARDTREE.CDREVISION AND STSCORECARD.FGCURRENT = 1) /* OBS 4 */
INNER JOIN STSCORECARDTREE OWNER ON(OWNER.CDSCORECARDTREE = STSCORECARDTREE.CDSCORECARDTREEOWNER) /* OBS 5 */
LEFT OUTER JOIN STSCSTRUCTITEM ON(STSCSTRUCTITEM.CDSCSTRUCTITEM = STSCORECARDTREE.CDSCSTRUCTITEM AND STSCSTRUCTITEM.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCSTRUCTITEM.CDREVISION = STSCORECARDTREE.CDREVISION)
LEFT OUTER JOIN STSCMETRIC ON(STSCMETRIC.CDSCMETRIC = STSCORECARDTREE.CDSCMETRIC AND STSCMETRIC.CDSCORECARD = STSCORECARDTREE.CDSCORECARD AND STSCMETRIC.CDREVISION = STSCORECARDTREE.CDREVISION)
--N1
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER ON(SC_ELEMENT_OWNER.CDSCSTRUCTITEM = OWNER.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_ELEMENT_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 1) /* OBS 6 */
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER ON(ELEMENT_OWNER.CDSCOREITEM = SC_ELEMENT_OWNER.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER ON(SC_METRIC_OWNER.CDSCMETRIC = OWNER.CDSCMETRIC AND SC_METRIC_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_METRIC_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 2) /* OBS 7 */
LEFT OUTER JOIN STMETRIC METRIC_OWNER ON(METRIC_OWNER.CDMETRIC = SC_METRIC_OWNER.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER ON(SC_OWNER.CDSCORECARD = OWNER.CDSCORECARD AND SC_OWNER.CDREVISION = OWNER.CDREVISION AND OWNER.FGNODETYPE = 0) /* OBS 8 */
--N2
LEFT OUTER JOIN STSCORECARDTREE OWNER_2 ON(OWNER_2.CDSCORECARDTREE = OWNER.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_2 ON(SC_ELEMENT_OWNER_2.CDSCSTRUCTITEM = OWNER_2.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_ELEMENT_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_2 ON(ELEMENT_OWNER_2.CDSCOREITEM = SC_ELEMENT_OWNER_2.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_2 ON(SC_METRIC_OWNER_2.CDSCMETRIC = OWNER_2.CDSCMETRIC AND SC_METRIC_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_METRIC_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_2 ON(METRIC_OWNER_2.CDMETRIC = SC_METRIC_OWNER_2.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_2 ON(SC_OWNER_2.CDSCORECARD = OWNER_2.CDSCORECARD AND SC_OWNER_2.CDREVISION = OWNER_2.CDREVISION AND OWNER_2.FGNODETYPE = 0)
--N3
LEFT OUTER JOIN STSCORECARDTREE OWNER_3 ON(OWNER_3.CDSCORECARDTREE = OWNER_2.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_3 ON(SC_ELEMENT_OWNER_3.CDSCSTRUCTITEM = OWNER_3.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_ELEMENT_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_3 ON(ELEMENT_OWNER_3.CDSCOREITEM = SC_ELEMENT_OWNER_3.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_3 ON(SC_METRIC_OWNER_3.CDSCMETRIC = OWNER_3.CDSCMETRIC AND SC_METRIC_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_METRIC_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_3 ON(METRIC_OWNER_3.CDMETRIC = SC_METRIC_OWNER_3.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_3 ON(SC_OWNER_3.CDSCORECARD = OWNER_3.CDSCORECARD AND SC_OWNER_3.CDREVISION = OWNER_3.CDREVISION AND OWNER_3.FGNODETYPE = 0)
--N4
LEFT OUTER JOIN STSCORECARDTREE OWNER_4 ON(OWNER_4.CDSCORECARDTREE = OWNER_3.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_4 ON(SC_ELEMENT_OWNER_4.CDSCSTRUCTITEM = OWNER_4.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_ELEMENT_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_4 ON(ELEMENT_OWNER_4.CDSCOREITEM = SC_ELEMENT_OWNER_4.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_4 ON(SC_METRIC_OWNER_4.CDSCMETRIC = OWNER_4.CDSCMETRIC AND SC_METRIC_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_METRIC_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_4 ON(METRIC_OWNER_4.CDMETRIC = SC_METRIC_OWNER_4.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_4 ON(SC_OWNER_4.CDSCORECARD = OWNER_4.CDSCORECARD AND SC_OWNER_4.CDREVISION = OWNER_4.CDREVISION AND OWNER_4.FGNODETYPE = 0)
--N5
LEFT OUTER JOIN STSCORECARDTREE OWNER_5 ON(OWNER_5.CDSCORECARDTREE = OWNER_4.CDSCORECARDTREEOWNER)
LEFT OUTER JOIN STSCSTRUCTITEM SC_ELEMENT_OWNER_5 ON(SC_ELEMENT_OWNER_5.CDSCSTRUCTITEM = OWNER_5.CDSCSTRUCTITEM AND SC_ELEMENT_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_ELEMENT_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 1)
LEFT OUTER JOIN STSCOREITEM ELEMENT_OWNER_5 ON(ELEMENT_OWNER_5.CDSCOREITEM = SC_ELEMENT_OWNER_5.CDSCOREITEM)
LEFT OUTER JOIN STSCMETRIC SC_METRIC_OWNER_5 ON(SC_METRIC_OWNER_5.CDSCMETRIC = OWNER_5.CDSCMETRIC AND SC_METRIC_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_METRIC_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 2)
LEFT OUTER JOIN STMETRIC METRIC_OWNER_5 ON(METRIC_OWNER_5.CDMETRIC = SC_METRIC_OWNER_5.CDMETRIC)
LEFT OUTER JOIN STSCORECARD SC_OWNER_5 ON(SC_OWNER_5.CDSCORECARD = OWNER_5.CDSCORECARD AND SC_OWNER_5.CDREVISION = OWNER_5.CDREVISION AND OWNER_5.FGNODETYPE = 0)
WHERE STSCMETRIC.IDSCMETRIC = 'Identificador do indicador do Scorecard' /* OBS 9 */
OR STSCSTRUCTITEM.IDSCSTRUCTITEM = 'Identificador do elemento do Scorecard' /* OBS 10 */
|
Observações
▪1: O campo FGNODETYPE igual a 0 (zero) determina que o registro é do tipo scorecard, e que retornará o identificar e nome dele. ▪2: O campo FGNODETYPE igual a 1 (um) determina que o registro é do tipo elemento, e que retornará o identificar e nome dele. ▪3: O campo FGNODETYPE igual a 2 (dois) determina que o registro é do tipo indicador, e que retornará o identificar e nome dele. ▪4: O campo FGCURRENT igual a 1 (um) determina que será filtrado apenas registros de scorecard que estejam em scorecards na revisão vigente ou sem revisão. ▪5: Relação da tabela STSCORECARDTREE com a tabela STSCORECARDTREE, que usa o campo CDSCORECARDTREE com o campo CDSCORECARDTREEOWNER. Essa relação busca o nó pai do registro a ser procurado. A mesma relação é feita até o N5. ▪6: O campo FGNODETYPE igual a 1 (um) determina que será filtrado na estrutura do scorecard apenas registros que sejam de elementos do scorecard. ▪7: O campo FGNODETYPE igual a 2 (dois) determina que será filtrado na estrutura do scorecard apenas registros que sejam de indicadores do scorecard. ▪8: O campo FGNODETYPE igual a 0 (zero) determina que será filtrado na estrutura do scorecard apenas registros que seja o scorecard. ▪9: Identificador do indicador do scorecard a ser filtrado na consulta. ▪10: Identificador do elemento do scorecard a ser filtrado na consulta.
Campos utilizados na consulta
Nº
|
ID do campo
|
Tipo
|
Observação
|
1
|
N1
|
VARCHAR
|
Identificador e nome do registro em nível 1 ao pesquisado
|
2
|
N2
|
VARCHAR
|
Identificador e nome do registro em nível 2 ao pesquisado
|
3
|
N3
|
VARCHAR
|
Identificador e nome do registro em nível 3 ao pesquisado
|
4
|
N4
|
VARCHAR
|
Identificador e nome do registro em nível 4 ao pesquisado
|
5
|
N5
|
VARCHAR
|
Identificador e nome do registro em nível 5 ao pesquisado
|
|
