asked on
SELECT
DAVS.RECID as PRIRECID,
DAVSI.RECID as SECRECID,
DA.NAME,
DAVSI.DISPLAYVALUE,
dft.DESCRIPTION,bu.NAME As LocationDetails,dept.NAME As Dept, Cc.NAME as Costcentre
FROM
dbo.DIMENSIONATTRIBUTEVALUESET AS DAVS
LEFT OUTER JOIN
dbo.DIMENSIONATTRIBUTEVALUESETITEM AS DAVSI ON DAVS.RECID = DAVSI.DIMENSIONATTRIBUTEVALUESET
INNER JOIN
dbo.DIMENSIONATTRIBUTEVALUE AS DAV on DAV.RECID = DAVSI.DIMENSIONATTRIBUTEVALUE
INNER JOIN
dbo.DIMENSIONATTRIBUTE AS DA on DA.RECID = DAV.DIMENSIONATTRIBUTE
LEFT JOIN DimensionAttributeDirCategory dadc
ON dadc.DimensionAttribute = da.RecId
LEFT OUTER JOIN dbo.DIMENSIONFINANCIALTAG dft
ON davsi.DISPLAYVALUE = dft.VALUE
AND da.VIEWNAME = 'DimensionFinancialTag'
LEFT OUTER JOIN dbo.DIMATTRIBUTEOMDEPARTMENT dept
ON davsi.DISPLAYVALUE = dept.VALUE
AND da.VIEWNAME = 'DimAttributeOMDepartment'
LEFT OUTER JOIN dbo.DIMATTRIBUTEOMBUSINESSUNIT bu
ON davsi.DISPLAYVALUE = bu.VALUE
AND da.VIEWNAME = 'DimAttributeOMBusinessUnit'
LEFT OUTER JOIN dbo.DIMATTRIBUTEOMCOSTCENTER cc
ON davsi.DISPLAYVALUE = cc.VALUE
AND da.VIEWNAME = 'DimAttributeOMCostCenter'
Example resultset:PRIRECID | SECRECID | NAME | DISPLAYVALUE | DESCRIPTION | LocationDetails | Dept | Costcentre |
5637146826 | 5637146830 | E_Division | COR | NULL | NULL | Corporate Services | NULL |
5637146826 | 5637146826 | A_Location | 22229P | NULL | Balance Sheet | NULL | NULL |
5637146826 | 5637146827 | B_Business_Unit | BALHCE | NULL | NULL | NULL | Balance Sheet |
5637146826 | 5637146828 | C_Product_Service | ZBALCE | Balance Sheet | NULL | NULL | NULL |
5637146826 | 5637146829 | D_Portfolio | NDLS | Mining | NULL | NULL | NULL |