HSP_MEMBER（第3部分–全部维度信息）

--获取全部维度

 

SELECT
  o.OBJECT_NAME AS MEMBER_NAME
  ,op.OBJECT_NAME AS PARENT_NAME
  ,a1.ALIAS_NAME AS ALIAS_DEFAULT
  ,a2.ALIAS_NAME AS ALIAS_ENGLISH
  ,( SELECT LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA_LIST" FROM (SELECT 
      UDA_VALUE
      ,MEMBER_ID
    FROM
      HSP_MEMBER_TO_UDA mu
    INNER JOIN
      HSP_UDA u ON mu.UDA_ID = u.UDA_ID) muda WHERE muda.MEMBER_ID = m.MEMBER_ID) AS UDA
  ,CASE m.DATA_STORAGE
    WHEN 0 THEN 'Store Data'
    WHEN 1 THEN 'Never Share'
    WHEN 2 THEN 'Label Only'
    WHEN 3 THEN 'Shared Member'
    WHEN 4 THEN 'Dynamic Calc and Store'
    WHEN 5 THEN 'Dynamic'
    END AS DATA_STORAGE
  ,CASE m.DATA_TYPE
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Currency'
    WHEN 2 THEN 'Non-currency'
    WHEN 3 THEN 'Percentage'
    WHEN 4 THEN 'Enum'
    WHEN 5 THEN 'Date'
    WHEN 6 THEN 'Text'
    ELSE 'Unspecified'
    END AS DATA_TYPE
  ,CASE WHEN BITAND(m.USED_IN,1) = 1 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,6) = 6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,5) = 5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,4) = 4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,3) = 3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,2) = 2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,1) = 1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS PLAN1_CONSOL_OP
  ,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
  ,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
  ,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
FROM
  HSP_MEMBER m
INNER JOIN 
  HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
  HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
  HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
  ( SELECT
      oa.OBJECT_NAME AS ALIAS_NAME
      ,a.MEMBER_ID
    FROM
      HSP_ALIAS a
    INNER JOIN
      HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
    INNER JOIN
      HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
    WHERE
      oat.OBJECT_NAME = 'Default')
   a1 ON m.MEMBER_ID = a1.MEMBER_ID
LEFT JOIN
  ( SELECT
      oa.OBJECT_NAME AS ALIAS_NAME
      ,a.MEMBER_ID
    FROM
      HSP_ALIAS a
    INNER JOIN
      HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
    INNER JOIN
      HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
    WHERE
      oat.OBJECT_NAME = 'English')
   a2 ON m.MEMBER_ID = a2.MEMBER_ID
LEFT JOIN
  (SELECT
    MEMBER_ID
    ,mf.PLAN_TYPE
    ,CASE DATA_STORAGE
      WHEN 0 THEN 'Store Data'
      WHEN 1 THEN 'Never Share'
      WHEN 2 THEN 'Label Only'
      WHEN 3 THEN 'Shared Member'
      WHEN 4 THEN 'Dynamic Calc and Store'
      WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
    ,SOLVE_ORDER
    ,FORMULA
  FROM
    HSP_MEMBER_FORMULA mf
  LEFT JOIN
    (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
     SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
  WHERE
    p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
  (SELECT
    MEMBER_ID
    ,mf.PLAN_TYPE
    ,CASE DATA_STORAGE
      WHEN 0 THEN 'Store Data'
      WHEN 1 THEN 'Never Share'
      WHEN 2 THEN 'Label Only'
      WHEN 3 THEN 'Shared Member'
      WHEN 4 THEN 'Dynamic Calc and Store'
      WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
    ,SOLVE_ORDER
    ,FORMULA
  FROM
    HSP_MEMBER_FORMULA mf
  LEFT JOIN
    (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
     SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
  WHERE
    p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
WHERE
  od.OBJECT_NAME = 'Product'

查询结果