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')

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top