Nested case when parent depends on child in SQL Server

Question

I wanted to check when it comes to nested case where the parent depends on the child, is there any better way then to throw the query in the from clause.

Please mind the details of the logic, but this is the blueprint of what I want to accomplish and it works. The code just looks sloppy. Is there a better/neater way to accomplish this?

SELECT 
    *,
    CASE 
       WHEN b.NewCalcColRate IS NULL
          THEN 0 
          ELSE b.NewCalcColRate * 1000 
    END AS FinalCalcColRate
FROM
    (SELECT
         a.*,
         CASE 
            WHEN a.calcColRate IS NULL
               THEN 0 
               ELSE a.calcColRate * 100 
         END AS NewCalcColRate
     FROM
         (SELECT
              *,
              CASE 
                 WHEN f.AnnualCost IS NULL
                    THEN 0 
                    ELSE f.AnnualCost / 12 
              END AS calcColRate
          FROM 
              Rates) AS a
      ) AS b

@maSTAShuFu thanks for quick response. But the desired result is exactly what the above code would do. — imba22, Apr 18 '18 at 20:14
Would help to have sample data and expected result - not just the code you are currently using — Daniel Marcus, Apr 18 '18 at 20:15
yeah... but we are not sure if your code works until we see what's given and desired. — RoMEoMusTDiE, Apr 18 '18 at 20:15
looking at your code there is an `f` reference but it is not there — RoMEoMusTDiE, Apr 18 '18 at 20:16

S3S · Answer 1 · 2018-04-18T20:44:54.223

3

First,NewCalcColRate and calcColRate will never be NULLbecause in the nested CASE you are setting it to 0 when it is NULL(CASE WHEN f.AnnualCost IS NULL THEN 0), so that logic is pointless.

Also, you have this column with an f reference though it isn't aliased on the Rate table in that sub-query.

From what I can gather, this can be simplified to:

SELECT
    *,
    CASE 
        WHEN AnnualCost IS NULL
            THEN 0 
        ELSE 
            (AnnualCost / 12) * 1000000
    END AS calcColRate
FROM 
    Rate

Or, as Jabs pointed out...

SELECT 
   *,
   (ISNULL(AnnualCost,0)/12) * 1000000 
from 
   Rate

Another note, depending on the datatype of AnnualCost, you may want to consider dividing by a decimal so that you arne't doing INTEGER division and becoming a victim to lost precision.

(AnnualCost / 12.0) * 1000000.0

EXAMPLE

select 
     (1 / 12)   * 1000000  --Returns 0
    ,(1 / 12.0) * 1000000  --Returns 83333.000000

In the future, if your code works and you are only looking for improvements, I would post it on Code Review as it is more tailored towards this kind of request.

edited Apr 18 '18 at 20:44

answered Apr 18 '18 at 20:26

S3S

24,809
5
26
45

you can remove the case statement entirely with: isnull(AnnualCost,0)/12 * 1000000 – jabs Apr 18 '18 at 20:37
true @jabs, another way of writing the same logic. Ill add that – S3S Apr 18 '18 at 20:39
Why is that @Sami? Did i miss something (aside from the parentheses) – S3S Apr 18 '18 at 20:40
1

@scsimon No, you did not, I remove the comment, I thought it will be same as `SELECT (12/0)*100000` :) – Ilyes Apr 18 '18 at 20:43
Ah yes, the divide by 0, i thought that may be what you meant but didn't want to assume. – S3S Apr 18 '18 at 20:46

score 2 · Answer 2 · answered Apr 18 '18 at 20:48

There seems to be some important information missing from your question. If all you really want is the FinalCalcColRate for each row in your Rates table, then you can do it one step like @scsimon suggested in his/her answer:

select
    r.*,
    FinalCalcColRate = case when r.AnnualCost is null then 0 else r.AnnualCost / 12 * 100000 end
from
    dbo.Rates r;

Or a similar implementation using coalesce (or isnull in SQL Server; see this question for details on the differences):

select
    r.*,
    FinalCalcColRate = coalesce(r.AnnualCost / 12 * 100000, 0)
from
    dbo.Rates r;

However, one difference between @scsimon's query and your original is that the former outputs only the final calculated value while the latter also yields all of the intermediate values. It's not clear from your question whether consumers of this query will require those values or not. If they will, then you can include them simply enough:

select
    r.*,
    CalcColRate = coalesce(r.AnnualCost / 12, 0),
    NewCalcColRate = coalesce(r.AnnualCost / 12 * 100, 0),
    FinalCalcColRate = coalesce(r.AnnualCost / 12 * 100000, 0)
from
    dbo.Rates r;

There is some repetition of logic here—for instance, if you were to ever change the definition of CalcColRate, you would also have to manually change the expressions for NewCalcColRate and FinalCalcColRate—but it's small enough that I doubt it's worth worrying about. Nonetheless, if the construction in your original query was motivated by a desire to avoid such repetition, you can refactor the query to use CTEs instead of nested queries:

with CalcCTE as
(
    select
        r.*,
        CalcColRate = coalesce(r.AnnualCost / 12, 0)
    from
        dbo.Rates r
),
NewCalcColCTE as
(
    select
        c.*,
        NewCalcColRate = c.CalcColRate * 100
    from
        CalcCTE c
)
select
    n.*,
    FinalCalcColRate = n.NewCalcColRate * 1000
from
    NewCalcColCTE n;

This is obviously longer and arguably more difficult to understand than my previous query that defined all the values independently, but it does have the advantage that each step is built atop the last, and the CTE formulation tends to be a lot more readable than the equivalent set of nested queries since the steps are written in the order in which they're evaluated, whereas with a nested query you have to find the innermost point and work outward, which can get confusing in a hurry.

Good points on the output of the previous columns which would be implied by their select *. I'm not sure if they were doing that for simplification or what, so +1 from me — S3S, Apr 18 '18 at 20:50

Nested case when parent depends on child in SQL Server

2 Answers2