-1

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.

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

spacetanker
  • 119
  • 2
  • 15
  • `fmrexcep` is in a subquery with the alias `ind`. Try using that. And, only use single quotes for string and date constants. They can pose problems when used for column aliases. – Gordon Linoff Aug 21 '14 at 20:42
  • Gordon, thank you for the suggestion. As you suggested, I did change 'unit.fmrexcep' to ind.fmrexcep and it produced the same error. Since both the 'ind' and 'agg' aliases are enclosed within 'unit' (were it working properly) unit.fmr_excep should be correct. As for the use of single quotes, that's all I am using throughout my query. – spacetanker Aug 21 '14 at 22:50
  • Also, to eliminate that calculated field as the source of the problem, I also tried the query with the first line as SELECT unit.* This also produced the same error. – spacetanker Aug 21 '14 at 22:56

1 Answers1

0

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
    .
    .
    .
) AS unit
spacetanker
  • 119
  • 2
  • 15