attribute key was not found - MS SQL Server

Question

I got the following message on MS SQL Server (I'm translating from German):

"Table 'VF_Fact', column ORGUNIT_CD, Value: 1185. The attribute is ORGUNIT_CD. Row was dropped because attribute key was not found. Attribute: ORGUNIT_CD in the dimension 'Organization' from database 'Dashboard', Cube 'Box Cube'..."

I checked the fact table 'VF_Fact' and the column ORGUNIT_CD - there I was able to found the value '1185'. The column ORGUNIT_CD is defined as follows in the view:

CAST(  COALESCE( emp.ORGUNIT_CD, 99999999 ) AS char(8)) AS ORGUNIT_CD,

In addition the view retrieves the column from L_Employee_SAP TABLE, where ORGUNIT_CD is defined as follows:

[ORGUNIT_CD] [char](8) NOT NULL,

AND the value I find here is not '1185' but '00001185'.

The Fact table 'VF_Fact' is connected with the table L_ORG in which the column ORGUNIT_CD is defined as follows:

[ORGUNIT_CD] [char](8) NOT NULL,

This table hast the following value in the ORGUNIT_CD column: '00001185'.

Can anyone please explain, why am i getting this error, and how to remove it?

Answer 1

COALESCE function is dropping the leading zeroes. If you are checking for nulls you can do this and it will keep the zeroes. CAST (ISNULL( emp.ORGUNIT_CD, 99999999 ) as char(8))

Answer 2

From this answer:

COALESCE:

Return Types

Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

(Emphasis added). int had a higher precedence than varchar, so the return type of your COALESCE must be of type int. And obviously, your varchar value cannot be so converted.

As another answer noted, ISNULL() behaves differently: rather than return the data type with the highest precedence, it returns the data type of the first value (thus, @Aleem's answer would solve your issue). A more detailed explanation can be found here under the section "Data Type of Expression."

In your specific case, I'd actually recommend that you encase the alternative string in single quotes, thus tipping SQL Server off to the fact that you intend this to be a character field. This means your expression would be one of the following:

CAST (ISNULL( emp.ORGUNIT_CD, '99999999' ) as char(8))
CAST (COALESCE( emp.ORGUNIT_CD, '99999999' ) AS char(8))

The advantage of using quotes in this situation? If you (or another developer) comes back to this down the line and tries to change it to COALESCE() or do any other type of modification, it's still going to work without breaking anything, because you told SQL Server what data type you want to use in the string itself. Depending on what else you're trying to do, you might even be able to remove the CAST() statement entirely.

COALESCE( emp.ORGUNIT_CD, '99999999' )