July 1, 2019 at 4:30 pm
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
This is awful!
But it can be decomposed.
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
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
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
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