October 22, 2007 at 9:18 am
I'm trying to build a query where:
1) one of the columns is a subquery
2) another (computed) column needs to use the result of that subquery/column in its calculation
#1 is no problem, but I can't get the second column to refer to the first one?
For example,
SELECT
TABLE.COL1,
NEWCOL1=(SELECT.....),
NEWCOL2=NEWCOL1 /2 ***This is where it blows up, does not accept NEWCOL1 as valid***
FROM ...
WHERE ...
I know I can just retype the subquery after the equal sign in NEWCOL2, but when it's a fairly long one, it gets pretty confusing and hard to read. Is there a variable I can set up for that purpose, or something along those lines?
Thanks for your help
Gilles
October 22, 2007 at 9:25 am
Move the subquery into the from clause, so you can reference it as if it was another table. You'll likely get better performance too.
SELECT ... FROM Table1 INNER JOIN (SELECT columns from table 2 group by....) on table1.id=table2.id ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2007 at 9:36 am
Can you supply more code for the column select and from clause, etc? The community can probably give you better suggestions with that. That being said, here is another option, in addition to that suggested by Gail:
select Col1, Col1 / 2 as Col2
from
(
SELECT
TABLE.COL1,
NEWCOL1=(SELECT.....),
NEWCOL2=NEWCOL1 /2
FROM ...
WHERE ...
) as DerivedTable
This option requires that all of your columns have names, just to clear up any confusion. This doesn't address potential performance problems of column queries, as suggested by Gail.
Thanks,
Eric
October 22, 2007 at 11:03 am
Thanks for your help so far, but I think I need to rephrase my initial question:
Having given an alias to a derived/computed/subquery column in a query, how can I then use that alias inside the expression for a subsequent derived/computed/subquery column in that query?
When I try that, I get an "invalid column" type error message.
In other words,
SELECT
TABLE.COL,
COMPUTED_COL1=....,
COMPUTED_COL2=COMPUTED_COL1......
FROM...
WHERE...
Part in bold is where I get that error message.
My basic question, then, is how do I have to refer to COMPUTED_COL1 inside another expression? Just using COMPUTED_COL1 does not work.
October 22, 2007 at 11:36 am
The issue you're running into is the logical processing order. The SELECT list is processed after the FROM, ON, JOIN, WHERE, GROUP BY, WITH, HAVING clauses. Any reference to items within the select list that are dependent on other items within the select list will fail. Same problem as trying to refer to aliased columns in HAVING clause of GROUP BY (you know how you always have to refer to the aggregate again and not the pretty name you gave it when you typed it up).
Do what was suggested earlier, put the subselect into the FROM list as a derived table and then all the columns will be available to you. Performance will be better than the correlled sub query you're currently running.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2007 at 12:12 pm
I understand, thanks to all for your input!
Gilles
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply