SQL Formula Syntax - Part 2
Posted on 2004-08-04
Is it possible to nest 'CASE' statements in an SQL Column formula?
I tried do that and it doesn't seem to work. I'm not sure if I have a small error somewhere or if that is just impossible.
The statement I am trying is this:
Convert(money, (CASE WHEN ([DateCompleted] IS NOT NULL) THEN
(CASE WHEN ([JobType] = 'Partner) THEN round(([TotalRewardBase] * [PartnerPercent]),2)
ELSE round(([TotalRewardBase] * [PartnerPlusPercent]),2))
ELSE 0 END),2)
Is there something else that can be done to handle more complex logic?
I also tried introducing another column called PartnerPercentUsed. It has the following formula:
CASE WHEN ([JobType] = 'Partner') THEN [PartnerPercent] ELSE [PartnerPlusPercent] END
This works fine by itself, and (theoretically) allows me to simplify the other logic as follows:
Convert(money,(CASE WHEN ([DateCompleted] IS NOT NULL) THEN round(([TotalRewardBase] * [PartnerPercentUsed]),2) ELSE 0 END),2)
But this is not accepted.
Is it possible to have formula-driven columns which depend on other formula-driven columns?