My SQL code is creating some derived columns. Can I create derived columns from derived columns? I am including my SQL code below:
SELECT dbo.TRANS.DEPT "Pharmacy#", "Formulary" = SUM(CASE dbo.DRUG.FLAGS1_8 When 'Y' THEN 1 ELSE 0 END), "Non-Formulary" = SUM(CASE dbo.DRUG.FLAGS1_8 When 'N' THEN 1 ELSE 0 END), "FormularyCost" = SUM(CASE dbo.DRUG.FLAGS1_8 When 'Y' THEN dbo.TRANS.CALC_COST ELSE 0 END), "Non-FormularyCost" = SUM(CASE dbo.DRUG.FLAGS1_8 When 'N' THEN dbo.TRANS.CALC_COST ELSE 0 END) /*"Avg FormularyRXCost" = (FormularyCost/Formulary)*/ FROM dbo.TRANS INNER JOIN dbo.DRUG ON dbo.TRANS.NDC = dbo.DRUG.NDC WHERE (dbo.TRANS.[DATE] BETWEEN '20010301' AND '20010331') GROUP BY dbo.TRANS.DEPT HAVING (dbo.TRANS.DEPT = '001')
Yes. You can do this to a potentially unlimited depth; however, figuring it out and debugging gets to be a chore as you nest further and further. Consider for example that you have a table where you need to calculate a sum. This sum is then used as part of another sum which in turn is used again as part of another one, etc. Most people would accomplish this by using several temp tables and then joining temp tables together. This causes a very large physical write hit since you have to write everything to disk. It also violates the principle that I've learned over the years that 99.99% of the temp tables people use are not necessary. You eliminate it by realizing one thing about the SQL specification that isn't necessarily spelled out very clearly. The from clause contains a table. But, if you query a table, doesn't that result set in itself also look like a table? From this you could conclude that it is in fact possible to embed a result set straight into the from clause. You do so like the following:
select a.col1, sum(b.col2) from table1 a inner join (select col1, sum(col2) col2 from table2 group by col1) b on a.col1 = b.col1 group by a.col1
The key to making this work is that you enclose the inner select statement in parenthesis and also alias it. This causes SQL Server to treat it as a table with an alias and then all of the rules that apply to the use of tables and columns in a select statement apply.
This approach eliminates the 3 statements you would normally write that would include a temp table. Since the inner select statement is executed and utilized in memory, you don't get the large physical write hit you would normally take. Instead you will see a dramatic performance boost over using a temp table.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL Server guru is waiting to answer your toughest questions.
This was first published in April 2001