locked
2012 Business Rule Issue involving blanks/nulls RRS feed

  • Question

  • I'm running into an issue with a business rule (See logic below).  The rule works as long as there is a value in the DBA.attribute.  However if the value is null/blank this does not overwrite a previous value.   

    RuleConditionText
    HRNumber is equal to DBA.HRNumber.Code
    RuleActionText
        HRJobCode equals DBA.HRNumber.JobCode
        HRJobTitle equals DBA.HRNumber.JobDesc
        HRCostCenter equals DBA.HRNumber.CostCenter
        XNumber equals DBA.HRNumber.XNumber
        TermDate equals DBA.HRNumber.TermDate
        SSNumber equals DBA.HRNumber.SSNumber
        PhoneNumber equals DBA.HRNumber.PhoneNumber
        Email equals DBA.HRNumber.Email
        City equals DBA.HRNumber.City
        State equals DBA.HRNumber.State
    Thursday, July 5, 2012 1:28 PM

Answers

  • Spent some time looking into without much success.  I updated the @stagingMergeOverwrite to 2 and that sort of worked however it did cause an issue so I republished the rules and in doing so the variable reset to 0.  I was hoping that I could locate the, i'm guessing, either the stored procedure or XML script that creates the business rule stored procedure when you publish a rule.  I was thinking, if the @stagingMergeOverwrite = 2 then it would stage the null values?

    --This is the variable that is used as the ImportType when the rule seeds the Staging table . --------- DECLARE @stagingMergeOverwrite INT = 0; --------- -- Seed staging table with Code values. INSERT INTO stg.[OfficerCodeData_Leaf] (BatchTag, Batch_ID, ImportType, Code) SELECT DISTINCT @BatchTag, @NewBatchID, @stagingMergeOverwrite, brstg.MemberCode FROM #BRAssignmentStaging brstg WHERE brstg.IsChanged = 1

    To explain the rule in the original post.  We have employee numbers assigned to codes.  When an employee leaves the Employee Number {00000} assigned to a code is reset and all of the attributes are updated.  Because '00000' is just a default, 99% of the attributes are NULL and this clears out the code until a new Employee is assigned to it.

    Out of ideas on this one...except to load a space " " for the NULLS so the rule reads something?

    Saturday, July 7, 2012 4:01 AM

All replies

  • In the business rule stored procedure I've pinpointed the followings as to what may be the issue.  It looks as if the ImportType = 0 thus omitting the NULLS.

    The ImportType = @stagingMergeOverwrite param.

    DECLARE @ProcessOptionDefault INT = 1;

    DECLARE @ProcessOptionChangeValue INT = 2;

    DECLARE @ProcessOptionAssignments INT = @ProcessOptionDefault | @ProcessOptionChangeValue;

    DECLARE @ProcessOptionValidation INT = 4;

    DECLARE @ProcessOptionUI INT = 8;

    DECLARE @ProcessOptionExternalAction INT = 16;

    DECLARE @ProcessOptionLogging INT = 128;

    DECLARE @doAssignments BIT = 0;

    DECLARE @doValidation BIT = 0;

    DECLARE @doValidationLogging BIT = 0;

    DECLARE @doExternalAction BIT = 0;

    DECLARE @stagingMergeOverwrite INT = 0;

    DECLARE @ValidationStatus_NewAwaitingValidation INT = 0;

    DECLARE @ValidationStatus_AwaitingRevalidation INT = 4;

    DECLARE @ValidationStatus_ValidationFailed INT = 2;

    DECLARE @ValidationStatus_AwaitingDependentRevalidation INT = 5;I'm trying to locate the XML file that creates the BusinessRule procedure?

    Thursday, July 5, 2012 4:19 PM
  • Spent some time looking into without much success.  I updated the @stagingMergeOverwrite to 2 and that sort of worked however it did cause an issue so I republished the rules and in doing so the variable reset to 0.  I was hoping that I could locate the, i'm guessing, either the stored procedure or XML script that creates the business rule stored procedure when you publish a rule.  I was thinking, if the @stagingMergeOverwrite = 2 then it would stage the null values?

    --This is the variable that is used as the ImportType when the rule seeds the Staging table . --------- DECLARE @stagingMergeOverwrite INT = 0; --------- -- Seed staging table with Code values. INSERT INTO stg.[OfficerCodeData_Leaf] (BatchTag, Batch_ID, ImportType, Code) SELECT DISTINCT @BatchTag, @NewBatchID, @stagingMergeOverwrite, brstg.MemberCode FROM #BRAssignmentStaging brstg WHERE brstg.IsChanged = 1

    To explain the rule in the original post.  We have employee numbers assigned to codes.  When an employee leaves the Employee Number {00000} assigned to a code is reset and all of the attributes are updated.  Because '00000' is just a default, 99% of the attributes are NULL and this clears out the code until a new Employee is assigned to it.

    Out of ideas on this one...except to load a space " " for the NULLS so the rule reads something?

    Saturday, July 7, 2012 4:01 AM