CODEWITHSUNDEEP
sqlsql-serversubqueryrepeatcorrelated-subquery
4est
4est

Reputation: 3168

How to use value from sub select

I have one select:

select
b.[FiscalPeriod],
b.[Column2],b.[Current_Value],
(
    select TOP 1 [Column3] from [table1] b2 
    where month(b.ReportingPeriod) = month(DATEADD(month,1,b2.ReportingPeriod))
      AND YEAR(b.ReportingPeriod) = YEAR(DATEADD(month,1,b2.ReportingPeriod))
      AND b.id = b2.id
) as PREV_VALUE 
FROM [table1] b

Now I'm doing: (b.[Current_Value]-PREV_VALUE) as difference

but I got error:

Invalid column name 'PREV_VALUE'

I know that instead of PREV_VALUE, once again I can put sub select. But how to avoid repeat the select?

Upvotes: 0

Views: 521

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31795

You can turn your query into a derived table or CTE. Then you can treat the aliases like columns:

SELECT *, (Current_Value-PREV_VALUE) AS difference
FROM (
  Your current query
) q

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271171

You cannot access a table alias where it is defined. In your case, the best solution is probably outer apply:

select b.[FiscalPeriod], b.[Column2],b.[Current_Value], bb.PREV_VALUE
FROM [table1] b OUTER APPLY
     (select TOP 1 [Column3] as PREV_VALUE
      from [table1] b2 
      where month(b.ReportingPeriod) = month(DATEADD(month,1,b2.ReportingPeriod)) AND
            YEAR(b.ReportingPeriod) = YEAR(DATEADD(month,1,b2.ReportingPeriod)) AND
            b.id = b2.id
      order by ???
     ) bb

Then you can access the value more than once in the SELECT.

Note: When using TOP you should be using ORDER BY, so you should fill in the ???.

Upvotes: 3

Related Questions