Post reply

Reading derived column expression

  • July 1, 2019 at 4:30 pm

    #3658949

    Hi All,

    I have an SSIS package which contains a derived column transformation. One of the derived columns has an expression which I'm having difficulty reading/translating:

    ISNULL(ContractNumber) ? (ISNULL(PaidLossAmount) && ISNULL(CaseReserveAmount)) ? NULL(DT_CY) : (ISNULL(PaidLossAmount) ? 0 : PaidLossAmount) + (ISNULL(CaseReserveAmount) ? 0 : CaseReserveAmount) : PaidLossAmount

    Could someone please advise on how one would read this? I think the part that is confusing me are the multiple ?s. Would this be read as If ContractNumber is null, then PaidfLossAmount and CaseReserveAmount are null else PaidLossAmount is null, etc., … or would it be If ContractNumber is null, along with PaidLossAmount and CaseReserveAmount are null, then PaidLossAmount would be null, etc.? This is soo confusing me and any help would be greatly appreciated. Thanks!

  • July 1, 2019 at 4:49 pm

    #3658957

    This is awful!

    But it can be decomposed.

    1. If ContractNumber is NULL, then PaidLossAmount
    2. If PaidLossAmount and CaseReserveAmount are both NULL, then NULL
    3. Otherwise (PaidLossAmount + CaseReserveAmount) (with NULLs in either case converted to 0 to make the arithmetic work)

    I'd write this in C#, to avoid this monstrosity.

     

     

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 1, 2019 at 5:08 pm

    #3658977

    Thanks, Phil! I agree that this, is indeed, a monstrosity. I feel like I've had to bend my brain to make sense of it. So a question about your response. Regarding the 1st part of your decomposition, "If ContractNumber is NULL, then PaidLossAmount", are you getting this PaidLossAmount from the very last one in the expression after the colon?

    ISNULL(ContractNumber) ? (ISNULL(PaidLossAmount) && ISNULL(CaseReserveAmount)) ? NULL(DT_CY) : (ISNULL(PaidLossAmount) ? 0 : PaidLossAmount) + (ISNULL(CaseReserveAmount) ? 0 : CaseReserveAmount) : PaidLossAmount

    Are you associating the 2 parts in bold above with each other? Thanks for your help in deciphering this!

     

  • July 1, 2019 at 5:16 pm

    #3658980

    Yes I am, exactly right.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 1, 2019 at 5:23 pm

    #3658981

    Thank you, Phil. You're brilliant!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply