sql server query

Query 1


I have a query that looks like this:


If you notice in the FROM clause of the case statement, it's doing the select from TestTable that is aliased as tt2

When I run this query it works just fine.

SELECT Q1.[Col1]
      ,Q1.[Col2]
      ,Q1.[Col3]
      ,TotalCompensation
      ,CASE WHEN [Col1] = 0 THEN 0 ELSE AnnualCompensation - (
           SELECT [TotalGross] + [TotalBenefits]
           FROM [TestDatabase].[dbo].[TestTable] tt2
           WHERE
               tt2.[Col1] = Q1.[Col1] AND
               tt2.[Col2] = Q1.[Col2] AND
               tt2.[Col3] = Q1.[Col3] - 1
           ) END AS TotalCompDiff
FROM FROM [TestDatabase].[dbo].[TestTable] tt2
CROSS APPLY (
    SELECT [TotalGross] + [TotalBenefits] AS AnnualCompensation
) AS cross_apply_1

Select allOpen in new window

Query 2

Now I'm using a very similar query that looks like this:

SELECT Q1.[Col1]
      ,Q1.[Col2]
      ,Q1.[Col3]
      ,TotalCompensation
    ,CASE WHEN [Col1] = 0 THEN 0 ELSE AnnualCompensation - (
           SELECT [TotalGross] + [TotalBenefits]
           FROM Q1
           WHERE
               tt2.[Col1] = Q1.[Col1] AND
               tt2.[Col2] = Q1.[Col2] AND
               tt2.[Col3] = Q1.[Col3] - 1
           ) END AS TotalCompDiff
FROM
(
--- start of query 1 -------------------------------------------


--- end of query 1 ---------------------------------------------
) Q1
CROSS APPLY (
    SELECT [TotalGross] + [TotalBenefits] AS AnnualCompensation
) AS cross_apply_1

Select allOpen in new window

In this query, my main select statement gets its data from a query that goes in between the comments lines I'm showing in green. I didn't show the query in between the comment lines for simplicity.

Now in my case statement in this query my select statement is grabbing the column from my query which I called Q1.

When I run this query it didn't work. I don't think I'm writing the from clause in my cases statement correctly to reference my query Q1.

Right now I have this:

           SELECT [TotalGross] + [TotalBenefits]
           FROM Q1


Anyone know how I should correctly be referencing my query in my Case Statement?
p1.jpg
p2.jpg