Reading derived column expression

  • daniness

    SSCrazy

    Points: 2890

    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!

  • Phil Parkin

    SSC Guru

    Points: 244578

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • daniness

    SSCrazy

    Points: 2890

    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!

     

  • Phil Parkin

    SSC Guru

    Points: 244578

    Yes I am, exactly right.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • daniness

    SSCrazy

    Points: 2890

    Thank you, Phil. You're brilliant!

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

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