Idiot Question on aliases - 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)

Idiot Question on aliases

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2006-03-06 : 04:41:45

Hi,

What's wrong with this?

SELECT
i.networkId,
[First Name],
[Last Name],
d.name AS deptname,
g.name AS groupname,
p.Phone,
g.fax,
g.Id,
g.departmentIdF
FROM
individualTab AS i, grouptab as g, departmenttab as d, [Planet Tab] as p
WHERE
d.id = g.departmentidF AND
i.groupidF = g.id AND
p.[network id] = i.networkid AND
i.active = 0 AND d.id <> 22 AND
([First Name] = '%thrower%' OR [Last Name] LIKE '%thrower%' OR Phone LIKE '%thrower%' OR deptname = '%thrower%' OR groupname = '%thrower%')
ORDER BY p.[last name] ASC

It's sating that "deptname" and "groupname" are invalid and I can't for the life of me see why. I suppose that's what happens when you let VB developers write SQL :).

Help gratefully recieved.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-06 : 04:48:03

you can't use column alias in the where clause, only in the ORDER BY

SELECT
i.networkId,
[First Name],
[Last Name],
d.name AS deptname,
g.name AS groupname,
p.Phone,
g.fax,
g.Id,
g.departmentIdF
FROM
individualTab AS i, grouptab as g, departmenttab as d, [Planet Tab] as p
WHERE
d.id = g.departmentidF AND
i.groupidF = g.id AND
p.[network id] = i.networkid AND
i.active = 0 AND d.id <> 22 AND
([First Name] = '%thrower%' OR [Last Name] LIKE '%thrower%' OR Phone LIKE '%thrower%' OR d.name = '%thrower%' OR g.name = '%thrower%')
ORDER BY p.[last name] ASC

----------------------------------
'KH'

mattt
Posting Yak Master

194 Posts

Posted - 2006-03-06 : 04:50:17

d'oh!

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources