Reading derived column expression

Question

Post reply

Reading derived column expression

daniness

SSCrazy

Points: 2890
More actions
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!

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

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

Phil Parkin SSC Guru Points: 247158 More actions · Answer 1

This is awful!

But it can be decomposed.

If ContractNumber is NULL, then PaidLossAmount
If PaidLossAmount and CaseReserveAmount are both NULL, then NULL
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.

daniness SSCrazy Points: 2890 More actions · Answer 2

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!