Q

Can I create derived columns from derived columns?

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

This was first published in April 2001
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close