I recently ran into an issue when trying to populate a staging table to load to a dimension in d/EPM V12.08 MT relational modeling. I needed to display the element id before the description whenever the id was numeric (leaf level elements). When the id was character-based (consolidated elements), we did not need to show the ID. My first thought was to use a ISNUMERIC() function within a CASE function in the SQL query to determine whether or not to add the id to the description. While testing my query, the query engine returned a message stating that ISNUMERIC is not supported. To get around this, I was able to use the TRY_CAST function to check if the value was numeric. Here is my example
SELECT
T1.SUMMARY_ACCT as ID,
CASE
WHEN TRY_CAST(T1.SUMMARY_ACCT AS INT) IS NOT NULL THEN
T1.SUMMARY_ACCT + ' - ' + T1.ACCOUNT_DESC
ELSE
T1.ACCOUNT_DESC
END AS NAME,
T1.LAST_UPDT_DATE, 'N' AS ELEMENTTYPE, 1.0 AS WEIGHT, 0 AS ORDERPOSITION,
T2.SUMMARY_ACCT as PARENTID
FROM CHS_LAWSON_ACCOUNT_STAGE T1 INNER JOIN CHS_LAWSON_ACCOUNT_STAGE T2 ON T1.PARENT_OBJ_ID = T2.OBJ_ID
WHERE T1.SUMMARY_ACCT NOT IN (SELECT T3.ID FROM CHS_LAWSON_ACCOUNT_EXCLUSIONS T3)
