Overview:
Command that returns data of indicators, users, and their permissions in indicators.
Related tables
TABLE
|
Description
|
Primary key (PK)
|
Connection tables
|
STSCORECARDTREE
|
Stores the scorecard structure
|
CDSCORECARDTREE
CDSCORECARD
CDREVISION
|
STSCORECARD
STSCSTRUCTITEM
STSCMETRIC
|
STSCORECARD
|
Stores the scorecards
|
CDSCORECARD
CDREVISION
|
STSCORECARDTREE
STSCSTRUCTITEM
STSCMETRIC
|
STSCSTRUCTITEM
|
Stores the scorecard elements
|
CDSCSTRUCTITEM
CDSCORECARD
CDREVISION
|
STSCOREITEM
STSCORECARD
STSCORECARDTREE
|
STSCMETRIC
|
Stores the scorecard indicators
|
CDSCMETRIC
CDSCORECARD
CDREVISION
|
STMETRIC
STSCORECARD
STSCORECARDTREE
|
STSCOREITEM
|
Stores the elements
|
CDSCOREITEM
|
STSCSTRUCTITEM
|
STMETRIC
|
Stores the indicators
|
CDMETRIC
|
STSCMETRIC
|
Oracle
SELECT
CASE
WHEN OWNER.FGNODETYPE = 0 /* NOTE 1 */
THEN SC_OWNER.IDSCORECARD || ' - ' || SC_OWNER.NMSCORECARD
WHEN OWNER.FGNODETYPE = 1 /* NOTE 2 */
THEN SC_ELEMENT_OWNER.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER.NMSCOREITEM
WHEN OWNER.FGNODETYPE = 2 /* NOTE 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) /* NOTE 4 */
INNER JOIN STSCORECARDTREE OWNER ON(OWNER.CDSCORECARDTREE = STSCORECARDTREE.CDSCORECARDTREEOWNER) /* NOTE 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) /* NOTE 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) /* NOTE 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) /* NOTE 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 = 'Scorecard indicator ID' /* NOTE 9 */
OR STSCSTRUCTITEM.IDSCSTRUCTITEM = 'Scorecard element ID' /* NOTE 10 */
|
Postgres
SELECT
CASE
WHEN OWNER.FGNODETYPE = 0 /* NOTE 1 */
THEN SC_OWNER.IDSCORECARD || ' - ' || SC_OWNER.NMSCORECARD
WHEN OWNER.FGNODETYPE = 1 /* NOTE 2 */
THEN SC_ELEMENT_OWNER.IDSCSTRUCTITEM || ' - ' || ELEMENT_OWNER.NMSCOREITEM
WHEN OWNER.FGNODETYPE = 2 /* NOTE 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) /* NOTE 4 */
INNER JOIN STSCORECARDTREE OWNER ON(OWNER.CDSCORECARDTREE = STSCORECARDTREE.CDSCORECARDTREEOWNER) /* NOTE 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) /* NOTE 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) /* NOTE 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) /* NOTE 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 = 'Scorecard indicator ID' /* NOTE 9 */
OR STSCSTRUCTITEM.IDSCSTRUCTITEM = 'Scorecard element ID' /* NOTE 10 */
|
SQL Server
SELECT
CASE
WHEN OWNER.FGNODETYPE = 0 /* NOTE 1 */
THEN SC_OWNER.IDSCORECARD + ' - ' + SC_OWNER.NMSCORECARD
WHEN OWNER.FGNODETYPE = 1 /* NOTE 2 */
THEN SC_ELEMENT_OWNER.IDSCSTRUCTITEM + ' - ' + ELEMENT_OWNER.NMSCOREITEM
WHEN OWNER.FGNODETYPE = 2 /* NOTE 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) /* NOTE 4 */
INNER JOIN STSCORECARDTREE OWNER ON(OWNER.CDSCORECARDTREE = STSCORECARDTREE.CDSCORECARDTREEOWNER) /* NOTE 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) /* NOTE 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) /* NOTE 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) /* NOTE 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 = 'Scorecard indicator ID' /* NOTE 9 */
OR STSCSTRUCTITEM.IDSCSTRUCTITEM = 'Scorecard element ID' /* NOTE 10 */
|
Notes
▪1: The FGNODETYPE field equal to 0 (zero) determines that the record is of the scorecard type, returning its ID # and name. ▪2: The FGNODETYPE field equal to 1 (one) determines that the record is of the element type, returning its ID # and name. ▪3: The FGNODETYPE field equal to 2 (two) determines that the record is of the indicator type, returning its ID # and name. ▪4: The FGCURRENT field equal to 1 (one) determines that only scorecard records that are in scorecards in the current revision or without revision will be filtered. ▪5: The relationship with the STSCORECARDTREE table, that uses the CDSCORECARDTREE field with the CDSCORECARDTREEOWNER field. This relationship searches for the upper node of the record being searched. The same relationship is made until N5. ▪6: The FGNODETYPE field equal to 1 (one) determines that only records that are scorecard elements will be filtered in the scorecard structure. ▪7: The FGNODETYPE field equal to 2 (two) determines that only records that are scorecard indicators will be filtered in the scorecard structure. ▪8: The FGNODETYPE field equal to 0 (zero) determines that only records that are the scorecard will be filtered in the scorecard structure. ▪9: ID # of the scorecard indicator to be filtered in the search. ▪10: ID # of the scorecard element to be filtered in the search.
Fields used on the view screen
#
|
Field ID
|
Type
|
Description
|
1
|
N1
|
VARCHAR
|
ID # and name of the level 1 record being searched
|
2
|
N2
|
VARCHAR
|
ID # and name of the level 2 record being searched
|
3
|
N3
|
VARCHAR
|
ID # and name of the level 3 record being searched
|
4
|
N4
|
VARCHAR
|
ID # and name of the level 4 record being searched
|
5
|
N5
|
VARCHAR
|
ID # and name of the level 5 record being searched
|
|
