Updating via tblStgMemberAttribute - Set AttributeValue to NULL

Question

Hello everyone,

I defined an entity in MDS with an additional field of type numeric .  (Let's call it MyNumber )
Data are already loaded to that entity, via tables: tblStgMember and tblStgMemberAttribute.

I need to update that numeric field, but for some members, I need to set it to NULL.
I tried to put several values in tblStgMemberAttribute:

• AttributeName = MyNumber - AttributeValue = 'NULL'
• AttributeName = MyNumber - AttributeValue = ''
• AttributeName = MyNumber - AttributeValue = 'De-Activated'

Unfortunately, none of those values are accepted.  I always get the error code ERR210025.

Do you know how can I update a numeric field to NULL via the table tblStgMemberAttribute ?

Answer 1

Hi Guillaume,

ERR210025 : AttributeValue must be a number

numeric in MDS is not a nullable type and :

 

You can activate/desactivate a member

but you cannot desactivate an attribute

 

http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

Regards,

---

Xavier Averbouch

Answer 2

Thanks for your quick reply.

If I understand correctly, the only way to update an attribute to NULL is

1. De-Activating the whole member
2. Re-Inserting the member without specifying the column that I want to be null

Is it right ?

Answer 3

Exactly!

Regards,

---

Xavier Averbouch

Answer 4

Issue is in udpStagingMemberAttributeSave in lines

360->369 (Just search for "ERR210025" in the proc)

ISNUMERIC will return 0 (Zero) when the paramater is NULL, causeing a true on NULL, generating the error in Batch and not processing the line.

Correct by changeing:

AND ISNUMERIC(vStage.Attribute_Value) = 0  ; 

To:

AND ISNUMERIC(vStage.Attribute_Value) = 0 and vStage.Attribute_Value is Not NULL ; 

This will force a "False" on the error check and life goes on normally....

The adroit reader will rhetorically ask - "But what IF the field SHOULD be NULL?"

Numeric Fields cannot be assigned "Not Null", so it is never an issue....

Tony