Two Subqueries And A Calculated Field

[spacetanker1]

I'm developing a very large query to retrieve data from an Army Reserve data warehouse. The purpose of the query is to populate an Access database table that is then used for forms and reports within my organization. Due to the extensive number of fields involved and to limit the number of tables and aggregation required, the query is formed by combining two subqueries as follows (code examples omit the vast majority of the fields in the actual query for simplicity):

(SELECT SUM(cdruic6.asgn) AS 'asgn', cdruic6.uic
FROM cdruic6
LEFT OUTER JOIN c2final ON c2final.uic = cdruic6.uic
WHERE c2final.bdeuic = 'W71J'
GROUP BY cdruic6.uic) AS agg,
(SELECT SUM(CASE WHEN medpros.fmr_excep <> '' THEN 1 ELSE 0 END) AS 'fmrexcep', ldr_book.uic
FROM ldr_book
LEFT OUTER JOIN medpros ON medpros.idunique = ldr_book.idunique
WHERE ldr_book.bdeuic = 'W71J'
GROUP BY ldr_book.uic) AS ind
WHERE agg.uic = ind.uic

The above query works exactly as desired, displaying all of the columns for the alias 'agg' first followed by all of the columns for the alias 'ind' for each row. I was happy with it until I needed to create a calculated field that involved a field from each table. My approach was to make the above a new subquery (alias 'unit') and create the new calculated field from the selected data as follows (the three vertical dots represent the query previous described):

SELECT unit.*, unit.asgn-unit.fmrexcep AS 'asgn_adjexc'
FROM
(SELECT
.
.
.
) AS unit

This query DOES NOT function, and therein is the problem I've been wrestling with for a few days. Keeping with this same theme I have tried various approaches to no avail. In the given example the server (SQL Server 2008) returns the following error:

The multi-part identifier "AGG.UIC" could not be bound.

Note that removing the calculated field from line 1 above produces an identical error. I'd appreciate an assist from someone more experienced with SQL. Obviously SQL Server doesn't understand the end-result I am trying to achieve, which is a table with the following columns:

asgn_adjexc | asgn | uic | fmrexcep | uic

Thank you. I'm behind-the-curve and sure the problem is something ridiculously simple.

Matt

 

[spacetanker1]

After grinding on this for several days before finally posting, this morning I had an epiphany and solved my own problem. For anyone else who may encounter this dilemma, the problem lay in trying to nest the two subqueries within a subquery. Removing the 'unit' alias from the code solved the problem. My final code:

SELECT *, asgn-fmrexcep AS 'asgn_adjexc'
FROM
(SELECT SUM(cdruic6.asgn) AS 'asgn', cdruic6.uic
FROM cdruic6
LEFT OUTER JOIN c2final ON c2final.uic = cdruic6.uic
WHERE c2final.bdeuic = 'W71J'
GROUP BY cdruic6.uic) AS agg,
(SELECT SUM(CASE WHEN medpros.fmr_excep <> '' THEN 1 ELSE 0 END) AS 'fmrexcep', ldr_book.uic
FROM ldr_book
LEFT OUTER JOIN medpros ON medpros.idunique = ldr_book.idunique
WHERE ldr_book.bdeuic = 'W71J'
GROUP BY ldr_book.uic) AS ind
WHERE agg.uic = ind.uic