using alias in where clause - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

using alias in where clause

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-01-24 : 16:53:01

select distinct
bb.businessname,
cc.clientcontractid,
cc.contractnumber,
(select sum(amount)A1 from billingschedule bs1 where billingdate between cc.startdate and cc.enddate and bs1.clientcontractid = cc.clientcontractid)as 'tt'

from clientcontract cc
inner join contact c on cc.salesrepcontactid = c.contactid
inner join business b on c.businessid = b.businessid
inner join business bb on cc.businessid = bb.businessid
inner join venue v on cc.venueid = v.venueid
inner join contact c1 on cc.businessid = c1.businessid and c1.deletedate is null
inner join billingschedule bs on cc.clientcontractid = bs.clientcontractid and bs.deletedate is null
where tt.amount = 0

What I get is an error that says "The column prefix 'tt' does not match with a table name or alias name used in the query."

But I aliased my sub select query as "tt"
What I need is: after the subselect amount is calculated I want to get the values where the total is 0 .

Any help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 17:09:11

[code]
select distinct
bb.businessname,
cc.clientcontractid,
cc.contractnumber,
~~(select sum(amount)A1 from billingschedule bs1 where billingdate between cc.startdate and cc.enddate and bs1.clientcontractid = cc.clientcontractid)as 'tt'~~
0 as tt -- since you wanted tt = 0
from clientcontract cc
inner join contact c on cc.salesrepcontactid = c.contactid
inner join business b on c.businessid = b.businessid
inner join business bb on cc.businessid = bb.businessid
inner join venue v on cc.venueid = v.venueid
inner join contact c1 on cc.businessid = c1.businessid and c1.deletedate is null
inner join billingschedule bs on cc.clientcontractid = bs.clientcontractid and bs.deletedate is null
where (select sum(amount)A1 from billingschedule bs1 where billingdate between cc.startdate and cc.enddate and bs1.clientcontractid = cc.clientcontractid) = 0
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources