Цитата:
Сообщение от
mazzy
серьезно?
опять же со всем восхищением к изворотливости ума.
но задача тривиальной фильтрации действительно требует вот такого кода?
серьезно?!
нам тут не до шуток!!! я тут вот заглянул в стандартный DimensionSetEntity, а там... а там... а там вот чо:
X++:
/// Each enterprise can expose different dimension attributes. Therefore, we have determine what dimension attributes
/// columns are exposed, then concatenate the fields separated by the account delimiter.
/// Example: dimensionField1 - + dimensionField2 - + dimensionField3 ...
/// </remarks>
private static server str displayValue()
{
str displayValueSql;
str accountDelimiterSql;
str dimensionValueColumns;
str accountDelimiterEscapedSql;
SysDictEnum delimiterDictEnum = new SysDictEnum(enumnum(DimensionSegmentDelimiter));
int enumValue = delimiterDictEnum.firstValue();
for (int i = 1; i <= delimiterDictEnum.values(); i++)
{
str enumLabel = delimiterDictEnum.index2Label(enumValue);
accountDelimiterSql += strfmt(
"WHEN %1 THEN '%2'\n", delimiterDictEnum.index2Value(enumValue), enumLabel);
accountDelimiterEscapedSql += strfmt(
"WHEN %1 THEN '%2'\n", delimiterDictEnum.index2Value(enumValue), DimensionSetEntity::generateEscapedDelimiter(enumLabel));
enumValue = delimiterDictEnum.nextValue(enumValue);
}
// Get the list of all dimension attributes that have metadata columns
dimensionValueColumns = DimensionSchemaAndDataSynchronizationUtility::getSynchronizedDimensionValueColumnNames(
tablestr(DimensionAttributeValueSet));
if (strLen(dimensionValueColumns) == 0)
{
// No table extension columns.
displayValueSql = SysComputedColumn::returnLiteral('');
}
else
{
str errorMessage = strReplace("@Dimension:DimensionEntitiesDimensionStructureNotCreated", "'", "''");
LanguageId systemLanguageId = SystemParameters::find().SystemLanguageId;
// Determine the error message based on system language
if (systemLanguageId != '')
{
errorMessage = strReplace(SysLabel::labelId2String2("@Dimension:DimensionEntitiesDimensionStructureNotCreated", systemLanguageId), "'", "''");;
}
//Create view sql
displayValueSql = strfmt(
@"
SELECT (
SELECT CASE WHEN
NonNullDisplayValue IS NOT NULL
THEN NonNullDisplayValue
ELSE '%4'
END
FROM
(
-- Remove initial delimiter
SELECT STUFF(
(SELECT
-- Prepend with delimiter and escape internal delimiters
DIMENSIONSEGMENTDELIMITER + COALESCE(
REPLACE(REPLACE(DIMLIST.DISPLAYVALUE, '\', '\\'),
DIMENSIONSEGMENTDELIMITER,
DIMENSIONSEGMENTDELIMITERESCAPED),
'')
FROM
(SELECT TOP (1) DH.RECID
FROM DIMENSIONHIERARCHY DH
JOIN DIMENSIONHIERARCHYINTEGRATION DHI
ON DHI.DIMENSIONHIERARCHY = DH.RECID
AND DHI.ISDEFAULT = 1
AND DHI.PARTITION = T1.PARTITION
WHERE
DH.PARTITION = T1.PARTITION
AND DH.STRUCTURETYPE = %2
) AS DH
JOIN DIMENSIONHIERARCHYLEVEL DHL
ON DHL.DIMENSIONHIERARCHY = DH.RECID AND DHL.PARTITION = T1.PARTITION
JOIN DIMENSIONATTRIBUTE DA
ON DA.RECID = DHL.DIMENSIONATTRIBUTE AND DA.PARTITION = T1.PARTITION
LEFT JOIN
(SELECT DIMENSIONATTRIBUTE, DISPLAYVALUE
FROM
(SELECT * FROM DIMENSIONATTRIBUTEVALUESET DAVS
WHERE DAVS.PARTITION = T1.PARTITION AND DAVS.RECID = T1.RECID) P
UNPIVOT
(DISPLAYVALUE for DIMENSIONATTRIBUTE IN (%3))
AS DIMENSIONVALUECOLUMNNAME
) AS DIMLIST
ON
DIMLIST.DIMENSIONATTRIBUTE = REPLACE(DA.DIMENSIONVALUECOLUMNNAME COLLATE Database_Default, '.', '$')
ORDER BY DHL.DIMENSIONHIERARCHY, DHL.LEVEL_
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'NVARCHAR(MAX)'),
1, LEN(DIMENSIONSEGMENTDELIMITER), '')
AS NonNullDisplayValue)
AS DisplayValueFormat)
FROM
(SELECT TOP(1)
--Determine the account delimiter
CASE DIMENSIONSEGMENTDELIMITER
%1
END AS DIMENSIONSEGMENTDELIMITER,
--Determine the account delimiter escaped
CASE DIMENSIONSEGMENTDELIMITER
%5
END AS DIMENSIONSEGMENTDELIMITERESCAPED
FROM DIMENSIONPARAMETERS
WHERE DIMENSIONPARAMETERS.PARTITION = T1.PARTITION
) AS DIMENSIONPARAMETERS
",
accountDelimiterSql,
enum2int(DimensionHierarchyType::DataEntityDefaultDimensionFormat),
dimensionValueColumns,
errorMessage,
accountDelimiterEscapedSql);
}
return displayValueSql;
}