SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Two Subqueries and a Calculated Field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spacetanker
Starting Member

2 Posts

Posted - 08/22/2014 :  11:53:58  Show Profile  Reply with Quote
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 the above query DOES NOT resolve the error. Running the query without that calculated field produces the same error message.

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
Starting Member

2 Posts

Posted - 08/22/2014 :  12:28:50  Show Profile  Reply with Quote
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
Go to Top of Page

MichaelJSQL
Posting Yak Master

167 Posts

Posted - 08/22/2014 :  12:35:24  Show Profile  Reply with Quote
something like this might work for you, but can't really tell a sit is hard for me to follow your post. If you include table structures it makes it easier to test. The following uses CTEs and a join between them.

;With agg
AS
(
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
)
,ind
AS
(
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
)

SELECT A.asgn-i.fmrexcep asgn_adjexc ,A.asgn ,A.uic ,I.fmrexcep, I.uic
FROM agg A
INNER JOIN ind I ON A.uic = I.uic -- not sure if you want inner , left , right or cross
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000