Not to sound like a broken record, but…a few disclaimers:
- This is the fun stuff…that voids your warranty. Not that you have to tell Oracle that you poke around here, but if you do, they will blame anything and everything that’s wrong on you for being in there.
- The content here has been tested and validated against 11.1.2.3.500 and 11.1.2.4.
- The content here has been tested and validated against Microsoft SQL Server and Oracle 11g.
- The content here is based on the Vision sample application.
- The content here is totally unsupported by Oracle (in case you missed the first bullet).
We’ve finally made it to the last part in our series on the HSP_MEMBER table (and all of the many associated tables)! In this post, we’ll take everything that we’ve learned and combine it to get a full extract of a single custom dimension for a single plan type. In our SQL Server query, this is a pretty straight forward operation. We simply combine the queries from the prior five parts of this series.
SQL Server:
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
,REPLACE(REPLACE(REPLACE((
SELECT
ou.UDA_VALUE AS UDA
FROM
HSP_MEMBER_TO_UDA u
INNER JOIN
HSP_UDA ou ON ou.UDA_ID = u.UDA_ID
WHERE
u.MEMBER_ID = m.MEMBER_ID FOR XML Raw)
, '"/><row UDA="', ', '), '<row UDA="', ''), '"/>', '' )
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 m.USED_IN & 1 = 1 THEN
CASE
WHEN m.CONSOL_OP & 6 = 6 THEN '^'
WHEN m.CONSOL_OP & 5 = 5 THEN '~'
WHEN m.CONSOL_OP & 4 = 4 THEN '%'
WHEN m.CONSOL_OP & 3 = 3 THEN '/'
WHEN m.CONSOL_OP & 2 = 2 THEN '*'
WHEN 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
,at.ATTRIBUTE_NAME AS ATTRIBUTE_TEXT
,ab.ATTRIBUTE_NAME AS ATTRIBUTE_BOOLEAN
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 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 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
LEFT JOIN
(SELECT
m.MEMBER_ID
,amo.OBJECT_NAME AS ATTRIBUTE_NAME
FROM
HSP_MEMBER m
INNER JOIN
HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
INNER JOIN
HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
INNER JOIN
HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
WHERE
ado.OBJECT_NAME = 'AttrText') at ON m.MEMBER_ID = at.MEMBER_ID
LEFT JOIN
(SELECT
m.MEMBER_ID
,amo.OBJECT_NAME AS ATTRIBUTE_NAME
FROM
HSP_MEMBER m
INNER JOIN
HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
INNER JOIN
HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
INNER JOIN
HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
WHERE
ado.OBJECT_NAME = 'AttrBoolean') ab ON m.MEMBER_ID = ab.MEMBER_ID
WHERE
od.OBJECT_NAME = 'Product'
It makes for a long query…but effective. This query will give us all of our properties for our first plan type. And here are the results:

So SQL Server wasn’t too bad. How about Oracle? Well…that’s a different story. There are two ways to approach this query on the Oracle side. Option 1: combine all of the queries like we did in SQL Server. Sounds easy enough until you remember back to Part 3 of our series where we first used the LISTAGG function. That function makes this process a pain. Instead of just combining the queries together, we have to add everything to our group by as well. Here it is for fun:
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
,LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA LIST"
,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
,dbms_lob.substr(CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END,4000,1) 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
UDA_VALUE
,MEMBER_ID
FROM
HSP_MEMBER_TO_UDA mu
INNER JOIN
HSP_UDA u ON mu.UDA_ID = u.UDA_ID
) mu ON m.MEMBER_ID = mu.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'
GROUP BY
o.OBJECT_NAME
,op.OBJECT_NAME
,od.OBJECT_NAME
,a1.ALIAS_NAME
,a2.ALIAS_NAME
,m.DATA_STORAGE
,m.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
,dbms_lob.substr(CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END,4000,1)
,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END
,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END
It’s ugly, but it works. There was a problem with just adding everything to the GROUP BY. Member formulas are of type CLOB which of course can not be used in a GROUP BY. So what do we do? We instead use a substring function to convert the CLOB to a 4,000 character VARCHAR. This works great…assuming you don’t have any large formulas. So this isn’t a great solution.
So what do we do? Option B. We take a step back and place our LISTAGG function into its own sub-query. This let’s us move the group by to a very small place and still reference our member ID. Here it is:
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'
This makes for a much shorter and less complex query. Well, mildly less complex as this requires not just a sub-query, but a nested sub-query. With either query, whether you choose the complex query with the big group by or the far simpler query with the nested sub-query, the results are the same:

And so the series on the HSP_MEMBER table (and all the other tables related) ends. But wait, there are more properties missing? The account dimension has more? The entity dimension has more? Tune in for yet another series, this time on the built-in dimensions.