User Tools

Site Tools


report_data_list

SQL로 레포트가 사용하고 있는 KPI,UNIVERSE,KPILINK 출력.

-- 레포트 아이템
SELECT 
  R.REPORT_ID,
  R.REPORT_KEY,
  R.REPORT_NAME,
  R.KPIOBJECT_SYS_ID,
  R.KPIOBJECT_KEY,
  R.KPIOBJECT_NAME,
  R.CON,
  NULL VIEW_ID, 
  NULL VIEW_KEY,  
  R.UNIVERSE_ID,
  U.UNIVERSE_KEY, 
  U.UNIVERSE_NM,
  NULL CON_KPIOBJECT_KEY,
  NULL CON_KPIOBJECT_NAME,
  NULL TARGET_EXPRESSION,
  NULL INQUIRY_TYPE
FROM (
  SELECT 
    R.REPORT_ID, 
    R.REPORT_KEY,
    R.REPORT_NAME,
    R.KPIOBJECT_SYS_ID,
    R.KPIOBJECT_KEY,
    R.KPIOBJECT_NAME,
    DATASET_STORE.UNIVERSE_ID,
    DATASET_STORE.CON
  FROM (
    SELECT 
      R.REPORT_ID,
      R.REPORT_KEY,
      R.REPORT_NAME,
      K.KPIOBJECT_SYS_ID,
      K.KPIOBJECT_KEY,
      K.KPIOBJECT_NAME
    FROM (
      SELECT 
        R.REPORT_ID,
        R.REPORT_KEY,
        R.REPORT_NAME,
        CELLOBJ.KPIOBJECT
      FROM 
        KONA_REPORT R LEFT OUTER JOIN (
          SELECT CELL.REPORT_ID, CELL.KPIOBJECT
          FROM KONA_REPORT_ITEM_CELL CELL INNER JOIN KONA_KPIOBJECT OBJ
          ON CELL.KPIOBJECT = OBJ.KPIOBJECT_SYS_ID
        ) CELLOBJ
      ON R.REPORT_ID = CELLOBJ.REPORT_ID
    ) R LEFT OUTER JOIN KONA_KPIOBJECT K 
    ON K.KPIOBJECT_SYS_ID = R.KPIOBJECT
  ) R LEFT OUTER JOIN 
    ( 
      SELECT '조회연결' CON, KPIOBJECT_SYS_ID, UNIVERSE_ID FROM KONA_KPIOBJECT_DATASET
      UNION ALL
      SELECT '저장연결' CON, KPIOBJECT_SYS_ID, UNIVERSE_ID FROM KONA_KPIOBJECT_DATASTORE
    ) DATASET_STORE
  ON R.KPIOBJECT_SYS_ID = DATASET_STORE.KPIOBJECT_SYS_ID
) R LEFT OUTER JOIN (
  SELECT UNIVERSE_ID, UNIVERSE_KEY, UNIVERSE_NM FROM KONA_UNIVERSE
) U
ON R.UNIVERSE_ID = U.UNIVERSE_ID
--ORDER BY REPORT_ID, KPIOBJECT_SYS_ID 
UNION ALL
-- 레포트 검색조건
SELECT 
  REPORT_ID,
  REPORT_KEY,
  REPORT_NAME,
  NULL KPIOBJECT_SYS_ID,
  NULL KPIOBJECT_KEY,
  NULL KPIOBJECT_NAME,
  '검색조건' CON,
  VIEW_ID, 
  VIEW_KEY,  
  UNIVERSE_ID,
  UNIVERSE_KEY,
  UNIVERSE_NM,
  NULL CON_KPIOBJECT_KEY,
  NULL CON_KPIOBJECT_NAME,
  NULL TARGET_EXPRESSION,
  NULL INQUIRY_TYPE
FROM (
  SELECT 
    BB.REPORT_ID, 
    BB.REPORT_KEY, 
    BB.REPORT_NAME, 
    AA.VIEW_ID,
    AA.VIEW_KEY, 
    AA.UNIVERSE_QUERYITEM_KEY, 
    AA.UNIVERSE_LABELITEM_KEY  
  FROM 
    KONA_REPORT_SEARCHVIEW AA LEFT OUTER JOIN KONA_REPORT BB
  ON AA.REPORT_ID = BB.REPORT_ID
  WHERE AA.UNIVERSE_QUERYITEM_KEY IS NOT NULL 
) REPORT LEFT OUTER JOIN (
  SELECT 
    U.UNIVERSE_ID, 
    U.UNIVERSE_KEY, 
    U.UNIVERSE_NM, 
    ITEM.UNIVERSE_ITEM_ID, 
    ITEM.CODE, 
    ITEM.NAME 
  FROM KONA_UNIVERSE_ITEM ITEM INNER JOIN KONA_UNIVERSE U
  ON ITEM.UNIVERSE_ID = U.UNIVERSE_ID
  WHERE U.UNIVERSE_ID IS NOT NULL
) UNIVERSE
ON UNIVERSE_ITEM_ID = UNIVERSE_QUERYITEM_KEY
--ORDER BY REPORT_ID,KPIOBJECT_SYS_ID,CON
UNION ALL
-- KPI연결
 
SELECT
  REPORT_ID,
  REPORT_KEY,
  REPORT_NAME,
  NULL KPIOBJECT_SYS_ID,
  NULL KPIOBJECT_KEY,
  NULL KPIOBJECT_NAME,
  'KPI연결' CON,
  NULL VIEW_ID, 
  NULL VIEW_KEY,  
  NULL UNIVERSE_ID,
  NULL UNIVERSE_KEY,
  NULL UNIVERSE_NM,
  KPIOBJECT_KEY AS CON_KPIOBJECT_KEY,
  KPIOBJECT_NAME AS CON_KPIOBJECT_NAME,
  TARGET_EXPRESSION,
  INQUIRY_TYPE
FROM (
  SELECT 
    REPORT_ID,
    REPORT_KEY,
    REPORT_NAME,
    KPIOBJECT_KEY,
    KPIOBJECT_NAME,
    TARGET_EXPRESSION,
    SOURCE_DATASET_ID
  FROM (
    SELECT 
      R.REPORT_ID,
      R.REPORT_KEY,
      R.REPORT_NAME,
      KK.TARGET_EXPRESSION,
      KK.SOURCE_KPIOBJECT_ID,
      KK.SOURCE_DATASET_ID
    FROM KONA_REPORT R INNER JOIN KONA_KPILINK KK
    ON R.REPORT_ID = KK.SOURCE_REPORT_ID
    --ORDER BY REPORT_ID
  ) RR LEFT OUTER JOIN KONA_KPIOBJECT OBJ
  ON RR.SOURCE_KPIOBJECT_ID = OBJ.KPIOBJECT_SYS_ID
) R LEFT OUTER JOIN KONA_KPIOBJECT_DATASET ST
ON R.SOURCE_DATASET_ID = ST.DATASET_SYS_ID
ORDER BY REPORT_ID,KPIOBJECT_SYS_ID,CON
report_data_list.txt · Last modified: 2013/08/14 13:57 by gpqzm