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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical 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
swendellAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.