Avoiding duplicates in IIF statement

Question

The problem is as follows:

I have this table NUMBERS where I have two columns of numbers:

+----------+----------+
| Number 1 | Number 2 |
+----------+----------+
|       12 |        3 |
|       20 |        7 |
|       17 |        5 |
+----------+----------+

Based on this table I execute an SQL SELECT statement where I want to display the sum of both numbers and whether the sum is bigger than 20 (the Interim_Result as well as the Result must be displayed)

+----------+----------+----------------+----------+
| Number_1 | Number_2 | Interim_Result | Result   |
+----------+----------+----------------+----------+
|       12 |        3 |             15 | under 20 |
|       20 |        7 |             27 | over 20  |
|       17 |        5 |             22 | over 20  |
+----------+----------+----------------+----------+

I would write a SQL statement like this:

SELECT 
Number_1,
Number_2,
Number_1 + Number_2 AS Interim_Result,
iif(Number_1 + Number_2 > 20, over 20, under 20) AS Result
FROM 
NUMBERS

This statement works, but I have duplicate code Number_1 + Number_2
is there a way I can avoid the duplicate code in the iif and just write:

SELECT 
Number_1,
Number_2,
Number_1 + Number_2 AS Interim_Result,
iif(Interim_Result > 20, over 20, under 20) AS Result
FROM 
NUMBERS

FYI I am using SQL Server

You'd have to move the calculation to a subquery. I wouldn't worry about it though. — GSerg, Oct 14 '19 at 07:55
Possible duplicate of [Using alias in query and using it](https://stackoverflow.com/questions/2055126/using-alias-in-query-and-using-it) — GSerg, Oct 14 '19 at 07:57
could you give me a coding example how i move it to a subquery? in my work, the Code is a bit more complex than just a+b :) — clem995, Oct 14 '19 at 07:57
You evaluate everything from performance point of view and maintenance point of view.Here repeating `Number_1 + Number_2` twice is almost negligible maintenance. So from performance point of view let it repeat twice. — KumarHarsh, Oct 14 '19 at 10:35

BarneyL · Accepted Answer · 2019-10-14T09:18:21.020

3

You can often use the APPLY operator to shorten code and reduce repetition.

SELECT 
Number_1,
Number_2,
I.Interim_Result AS Interim_Result,
iif(I.Interim_Result > 20, 'over 20', 'under 20') AS Result
FROM 
NUMBERS AS N
CROSS APPLY (SELECT N.Number_1 + N.Number_2 AS Interim_Result) AS I

edited Oct 14 '19 at 09:18

answered Oct 14 '19 at 08:03

BarneyL

1,332
8
15

1

Great approach; just needs a few quotes (which OP also left out) – Peter B Oct 14 '19 at 08:19

Martin · Answer 2 · 2019-10-14T08:03:05.777

2

You could achieve this using a subquery, and perhaps using CASE instead of IIF:

SELECT Number_1,
       Number_2,
       Interim_Result,
       CASE
         WHEN Interim_result > 20 THEN 'Over 20'
         ELSE 'Under 20'
       END AS Result
  FROM (
       SELECT Number_1,
              Number_2,
              Number_1 + Number_2 AS Interim_Result
         FROM NUMBERS
       ) a

Note: Code is untested but should put you on the right path

Edit ref use of CASE

As @clem995 has pointed out, you can happily use IIF instead of CASE:

SELECT Number_1,
       Number_2,
       Interim_Result,
       IIF(Interim_result > 20, 'Over 20', 'Under 20') AS Result
  FROM (
       SELECT Number_1,
              Number_2,
              Number_1 + Number_2 AS Interim_Result
         FROM NUMBERS
       ) a

My personal preference is to use CASE as I find it more legible and it works across multiple SQL languages, but IIF is totally valid in this scenario.

edited Oct 14 '19 at 08:03

answered Oct 14 '19 at 07:57

Martin

16,093
1
29
48

1

why use CASE instead of IIF? – clem995 Oct 14 '19 at 08:00
@clem995 I personally prefer the syntactic layout of `CASE` - to me it is more readible. However, it would make no difference as both will produce the same output. `CASE` of course will also work across different SQL databases – Martin Oct 14 '19 at 08:01
1

And in modern-talk we would probably use a `with` statement rather than a subquery, but it amount to the same thing. +1 for the case statement too! – TomC Oct 14 '19 at 08:19

Avoiding duplicates in IIF statement

2 Answers2