NOT NULL violation when inserting in latest views

Anonymous

Hello Anchor Modelers,

I've built an Anchor Model that contains several attributes for an anchor. Some of these attributes can contain NULL values in the source data. My perception is that Anchor Modeling does not insert NULL values in the Anchor Model tables.

The behavior I would expect from loading the latest views is that when an attribute has a NULL value the Instead Of triggers will handle the NULL value, by not inserting a record in the specific Anchor Model table. On the contrary I get a NOT NULL violation.

What is the best way to handle loading data through the Latest Views when attributes sometimes contain NULL values?

Thanks,

Tim

Roenbaeck

Tim, can you post an example insert statement (into the latest view) that reproduces this behavior? NULL values should be ignored by the trigger.

Looking at the generated code, I see lines like the following in the triggers:

WHERE

i.AC_GEN_GEN_Gender is not null;

We'll fix this if you can give us an example.

Anonymous

Hi Lars,

This merge statement to load the latest view resulted in the not NULL violation.

Declare @Metadata_ID int = 1

Declare @LastLoaded DateTime = DATEADD(day, -1, convert(date, getDate()))

MERGE INTO lAI_ActorInfo AS TARGET

USING

(

SELECT

CU.companyunit_type_id,

CU.companyunit_type,

CU.companyunit_last_changed,

CU.companyunit_date_start,

CU.CMPU_ID,

CU.companyunit_business_key,

CU.companyunit_name,

coalesce(FI.PlantDateInfoHectare,0) AS PlantDateInfoHectare,

coalesce(FI.PlantDateInfoHectareTotal,0) AS PlantDateInfoHectareTotal,

coalesce(FI.RSL_date_changed, getdate()) AS RSL_date_changed,

coalesce(FI.PlantDateNrOfTreesProd,0) AS PlantDateNrOfTreesProd,

FI.FamilyChildrenSchool,

FI.FamilyChildrenSchoolUFifteen,

FI.FamilyMembers,

coalesce(FI.OwnerSexeId,0) AS OwnerSexeId,

coalesce(FI.OwnerSexe,'NA') AS OwnerSexe,

FI.OwnerDateOfBirth,

coalesce(SA.OrgDetInFormalId,0) AS OrgDetInFormalId,

coalesce(SA.OrgDetInFormal,'NA') AS OrgDetInFormal,

coalesce(SA.OrgDetLevelId,0) AS OrgDetLevelId,

coalesce(SA.OrgDetLevel,'NA') AS OrgDetLevel,

case ltrim(SA.OrgDetWarehouse) when '' then 0 else 1 end OrgHasWarehouse

FROM

[SA_IN].dbo.VW_RPRT_DIM_COMPANY_UNIT CU

LEFT JOIN [SA_IN].dbo.VW_RPRT_SA_IN_DIM_FARMER_INFORMATION FI ON CU.CMPU_id = FI.RSL_CMPU_id_owner

LEFT JOIN [SA_IN].dbo.VW_RPRT_SA_IN_DIM_SC_ACTOR SA ON CU.CMPU_id = SA.RSL_CMPU_id_owner

WHERE FI.RSL_date_changed > @LastLoaded or CU.companyunit_last_changed > @LastLoaded

)

AS SOURCE

ON TARGET.AI_ID = SOURCE.CMPU_ID

WHEN MATCHED THEN UPDATE

SET

TARGET.Metadata_AI = @Metadata_ID,

TARGET.AI_TYP_ACT_ID = SOURCE.companyunit_type_id,

TARGET.AI_TYP_Metadata_ACT = @Metadata_ID,

TARGET.AI_TYP_ACT_ActorType = SOURCE.companyunit_type,

TARGET.AI_TYP_ChangedAt = SOURCE.companyunit_last_changed,

TARGET.Metadata_AI_TYP = @Metadata_ID,

TARGET.AI_NAM_ActorInfo_Name = ISNULL(SOURCE.companyunit_name, TARGET.AI_NAM_ActorInfo_Name),

TARGET.AI_NAM_ChangedAt = SOURCE.companyunit_last_changed,

TARGET.Metadata_AI_NAM = @Metadata_ID,

TARGET.AI_HAP_ActorInfo_HAinProd = SOURCE.PlantDateInfoHectare,

TARGET.AI_HAP_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_HAP = @Metadata_ID,

TARGET.AI_HEC_ActorInfo_Hectares = SOURCE.PlantDateInfoHectareTotal,

TARGET.AI_HEC_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_HEC = @Metadata_ID,

TARGET.AI_CHS_ActorInfo_ChildrenInSchool = SOURCE.FamilyChildrenSchool,

TARGET.AI_CHS_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_CHS = @Metadata_ID,

TARGET.AI_CUN_ActorInfo_ChildrenInSchoolUnder15 = SOURCE.FamilyChildrenSchoolUFifteen,

TARGET.AI_CUN_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_CUN = @Metadata_ID,

TARGET.AI_MEM_ActorInfo_TotalFamilyMembers = SOURCE.FamilyMembers,

TARGET.AI_MEM_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_MEM = @Metadata_ID,

TARGET.AI_GEN_GEN_ID = SOURCE.OwnerSexeId,

TARGET.AI_GEN_GEN_Gender = SOURCE.OwnerSexe,

TARGET.AI_GEN_Metadata_GEN = @Metadata_ID,

TARGET.AI_GEN_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_GEN = @Metadata_ID,

TARGET.AI_DOB_ActorInfo_DateOfBirth = SOURCE.OwnerDateOfBirth,

TARGET.AI_DOB_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_DOB = @Metadata_ID,

TARGET.AI_FOR_FOR_ID = SOURCE.OrgDetInFormalId,

TARGET.AI_FOR_FOR_FormalInformal = SOURCE.OrgDetInFormal,

TARGET.AI_FOR_Metadata_FOR = @Metadata_ID,

TARGET.AI_FOR_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_FOR = @Metadata_ID,

TARGET.AI_OGR_OGR_ID = SOURCE.OrgDetLevelId,

TARGET.AI_OGR_OGR_OrganizationGrade = SOURCE.OrgDetLevel,

TARGET.AI_OGR_Metadata_OGR = @Metadata_ID,

TARGET.AI_OGR_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_OGR = @Metadata_ID,

TARGET.AI_SDA_ActorInfo_StartDate = SOURCE.companyunit_date_start,

TARGET.Metadata_AI_SDA = @Metadata_ID,

TARGET.AI_WHO_ActorInfo_HasWarehouse = SOURCE.OrgHasWarehouse,

TARGET.AI_WHO_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_WHO = @Metadata_ID,

TARGET.AI_TRE_ActorInfo_TreesInProd = SOURCE.PlantDateNrOfTreesProd,

TARGET.AI_TRE_ChangedAt = SOURCE.RSL_date_changed,

TARGET.Metadata_AI_TRE = @Metadata_ID

WHEN NOT MATCHED BY TARGET THEN INSERT (

Metadata_AI,

AI_TYP_ACT_ID,

AI_TYP_ACT_ActorType,

AI_TYP_Metadata_ACT,

AI_TYP_ChangedAt,

Metadata_AI_TYP,

AI_ID,

AI_REG_ActorInfo_RegistrationID,

Metadata_AI_REG,

AI_NAM_ActorInfo_Name,

AI_NAM_ChangedAt,

Metadata_AI_NAM,

AI_HAP_ActorInfo_HAinProd,

AI_HAP_ChangedAt,

Metadata_AI_HAP,

AI_HEC_ActorInfo_Hectares,

AI_HEC_ChangedAt,

Metadata_AI_HEC,

AI_CHS_ActorInfo_ChildrenInSchool,

AI_CHS_ChangedAt,

Metadata_AI_CHS,

AI_CUN_ActorInfo_ChildrenInSchoolUnder15,

AI_CUN_ChangedAt,

Metadata_AI_CUN,

AI_MEM_ActorInfo_TotalFamilyMembers,

AI_MEM_ChangedAt,

Metadata_AI_MEM,

AI_GEN_GEN_ID,

AI_GEN_GEN_Gender,

AI_GEN_Metadata_GEN,

AI_GEN_ChangedAt,

Metadata_AI_GEN,

AI_DOB_ActorInfo_DateOfBirth,

AI_DOB_ChangedAt,

Metadata_AI_DOB,

AI_FOR_FOR_ID,

AI_FOR_FOR_FormalInformal,

AI_FOR_Metadata_FOR,

AI_FOR_ChangedAt,

Metadata_AI_FOR,

AI_OGR_OGR_ID,

AI_OGR_OGR_OrganizationGrade,

AI_OGR_Metadata_OGR,

AI_OGR_ChangedAt,

Metadata_AI_OGR,

AI_SDA_ActorInfo_StartDate,

Metadata_AI_SDA,

AI_WHO_ActorInfo_HasWarehouse,

AI_WHO_ChangedAt,

Metadata_AI_WHO,

AI_TRE_ActorInfo_TreesInProd,

AI_TRE_ChangedAt,

Metadata_AI_TRE

)

VALUES (

@Metadata_ID,

SOURCE.companyunit_type_id,

SOURCE.companyunit_type,

@Metadata_ID,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.CMPU_ID,

SOURCE.companyunit_business_key,

@Metadata_ID,

SOURCE.companyunit_name,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.PlantDateInfoHectare,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.PlantDateInfoHectareTotal,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.FamilyChildrenSchool,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.FamilyChildrenSchoolUFifteen,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.FamilyMembers,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.OwnerSexeId,

SOURCE.OwnerSexe,

@Metadata_ID,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.OwnerDateOfBirth,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.OrgDetInFormalId,

SOURCE.OrgDetInFormal,

@Metadata_ID,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.OrgDetLevelId,

SOURCE.OrgDetLevel,

@Metadata_ID,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.companyunit_date_start,

@Metadata_ID,

SOURCE.OrgHasWarehouse,

SOURCE.RSL_date_changed,

@Metadata_ID,

SOURCE.PlantDateNrOfTreesProd,

SOURCE.RSL_date_changed,

@Metadata_ID

);

In the mean time I've created a stored procedure that dynamically builds and executes the merge statements per attribute/knot table, based on metadata of target table. I first check whether the attribute/knot key is present in the target table: when not matched insert, when matched and value changed update; or when historized insert with new ChangedAt datetime. This works quite well and supports reuasabilty and automation, next step will be creating dynamic SQL for merge statements of ties and anchors.

Cheers,

Tim

Roenbaeck

I see what the error is now. It's in the update trigger that the problem arises. I had this notion of letting an update to NULL translate to a logical delete in concurrent-reliance-temporal modeling. On second thought, this would probably just add confusion, so I think I can just add a not null condition in the trigger, similar to the one in the insert trigger (and leave deletes for DELETE statements).

As a workaround, it is possible to use the following approach with an isnull wrapper in the WHEN MATCHED section of your MERGE:

http://pastebin.com/dyDhJbDr

Can anyone think of any objections to why the update trigger should not ignore null values? Will it break anyone's code?