How do I Reuse CASE logic

I would like to clean up my code and avoid having to make changes in multiple locations. This is my exact scenerio:

       SELECT
       CASE
       WHEN… Then
       WHEN… Then
      Just assume a ton of logic statements in here….
       END AS ContribToComm,
       
       SUM
          (
          CASE
       WHEN… Then
       WHEN… Then
       Just assume a ton of logic statements in here THAT ARE ALL THE EXACT SAME AS ABOVE !
            END
          )
       OVER (PARTITION BY L1.PayTo,MonthValue ORDER BY L1.PayTo, IssueDate, CheckNum) AS RunningTotal
   
       FROM….

How would you re-write this to avoid duplicating logic?
swendellAsked:
Who is Participating?
 
swendellConnect With a Mentor Author Commented:
Do i really need a TEMP Table?
this seems like it may work?

Select * ,  SUM (ContribToComm)
       OVER (PARTITION BY L1.PayTo,MonthValue ORDER BY L1.PayTo, IssueDate, CheckNum) AS RunningTotal
From
(    
   SELECT *,
       CASE
          WHEN… Then
          WHEN… Then
          Just assume a ton of logic statements in here….
       END AS ContribToComm
       FROM….
)
0
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
So you have a column that is computed and would then like to perform a SUM on the derived column.

I would store the intermediate result set (without the SUM) into a temporary table. Then, the final SELECT that returns the result would operate on this temporary table and would also have the SUM() on the pre-computed set (I.e. set of data where the CASE was already evaluated).

This way, you are reducing the CPU overheads to evaluate the CASE twice. Keep in mind though that you would need a fast I/O subsystem if you are using this approach.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Thanks for the points recommendation :)

I don't have SSMS in front of me right now, but the sub-query approach would also cause a table spool over to the tempdb given a sufficient amount of data (enough that cannot fit into the buffer pool). This should be seen in the execution plan.

My thought process was simply to give SQL Server the temp table rather than it requesting the system on the fly assuming that you are running some sort of a report that needs a large amount of data to be processed.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Scott PletcherSenior DBACommented:
Better to use CROSS APPLY to assign an alias name to the computed column.
SQL won't use a spool unless it's absolutely required, but even a spool will be more efficient that writing to a temp table first.

   SELECT t1.*, ..., SUM (ContribToComm) AS ContribToCommTotal
       OVER (PARTITION BY L1.PayTo,MonthValue ORDER BY IssueDate, CheckNum) AS RunningTotal
       FROM dbo.table1 t1
       CROSS APPLY (
             SELECT
                     CASE
                          WHEN… Then
                          WHEN… Then
                          Just assume a ton of logic statements in here….
                     END AS ContribToComm
       ) AS alias1
1
 
swendellAuthor Commented:
Scott P,
Thank you for the Cross Apply example. Can you generally explain, what / how Cross Apply saves over a plain SELECT or Temp Table approaches discussed further above?

And a brief conceptual explanation of Spooling in the context of this example.
0
 
Scott PletcherSenior DBACommented:
A CROSS APPLY (CA) is executed inline with the query, it's just a calculation step.  That is, there is no I/O overhead (unless the CA itself does I/O, which it does not do in this case).

I wouldn't expect SQL to use a spool for this query, so I can't really explain spooling in the context of this query.  Instead, I'd expect SQL to use a normal aggregate operation to compute the SUM values.

Since you have sample tables/data, please generate an "Estimated Query Plan" in SSMS and post it, and we can all see what actions SQL actually does do for this specific query :-).
0
 
swendellAuthor Commented:
Scott P.,
Not sure how to share , there is so much info to share so let me know if this is what you were looking for:
SQL Plan
This is the Select version I explained above (ID: 42541069)
Plan.pdf
0
 
Scott PletcherSenior DBACommented:
Interesting.  Did you get the same spooling from my version of the query?
0
 
swendellAuthor Commented:
Scott P,
I tried and go an error, maybe I did something wrong but what is DBO.Table1 in your example? I think I am missing something obvious.
I get error: 1 Invalid object name 'dbo.table1'. SQL.sql 32 8
I did what you did but my alias is named L2 not alias1

Mine looks more like:

SELECT *, SUM (ContribToComm) OVER (PARTITION BY PayTo,MonthValue ORDER BY PayTo, IssueDate, CheckNum) AS RunningTotal
  FROM dbo.table1 t1
  CROSS APPLY
       ( SELECT *,
             CASE ......
       ) AS L2
0
 
Scott PletcherSenior DBACommented:
You need to use whatever your actual table name is.  I had no idea so I used "table1" just as a placeholder/dummy name.
0
 
swendellAuthor Commented:
I am selecting from L2 but it gives an error
0
 
swendellAuthor Commented:
Nakul provided a direction that was a possible solution but not an example. I posted an example of a solution that works and is simple and clear w/o the need of a temporary table. Nakul deserves all the points and he did get me thinking about it from a different perspective
0
 
swendellAuthor Commented:
Sorry Scott P. I submitted close  then you responded after that but before they closed it and I'd like to give you points but don't know how.
And I still have not figured out the L2 thing above
0
 
Scott PletcherSenior DBACommented:
Np on the points, not worried about that.

I don't see where you've ever posted a query with "L2" in it for this q, so I can't help there either.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.