NOT NULL violation when inserting in latest views

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

NOT NULL violation when inserting in latest views

Tim Schiettekatte
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
Reply | Threaded
Open this post in threaded view
|

Re: NOT NULL violation when inserting in latest views

roenbaeck
Administrator
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.
Reply | Threaded
Open this post in threaded view
|

Re: NOT NULL violation when inserting in latest views

Tim Schiettekatte
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
Reply | Threaded
Open this post in threaded view
|

Re: NOT NULL violation when inserting in latest views

roenbaeck
Administrator
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?