Two Subqueries and a Calculated Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Two Subqueries and a Calculated Field

Author

Topic

spacetanker
Starting Member

2 Posts

Posted - 2014-08-22 : 11:53:58

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 - 2014-08-22 : 12:28:50

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

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-22 : 12:35:24

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources