declaring select information - 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

General SQL Server Forums

New to SQL Server Administration

declaring select information

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-13 : 17:14:15

This code works.

SELECT DISTINCT a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [smartview.exe],
d.Operating_System_Name_and0,
c.FilePath
FROM v_GS_SoftwareFile c INNER JOIN
v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = 'smartview.exe')
ORDER BY a.Name0

----------------------------------------------------------------

Ok, so I then wrote this code:

select displayname0 AS [Software Title]
from v_GS_ADD_REMOVE_PROGRAMS
WHERE (displayname0 LIKE '%DameWare%')

This too works.

-----------------------------------------------------------------

So now I want to mimic the code at the top. I want to use a.displayname0. So I wrote this.

select a.displayname0 AS [Software Title]
from v_GS_ADD_REMOVE_PROGRAMS
WHERE (a.displayname0 LIKE '%DameWare%')

this fails with this error.

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "a.displayname0" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.displayname0" could not be bound.

-------------------------------------------------------------------

I've read the SELECT statement online help but it did not cover this. Why would SELECT DISTINCT a.Name0 AS [Machine Name], work and this fails select a.displayname0 AS [Software Title] ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 17:18:07

you've not defined a in second case


select a.displayname0 AS [Software Title] 
from v_GS_ADD_REMOVE_PROGRAMS a
WHERE (a.displayname0 LIKE '%DameWare%')

you can use an alias only after defining it for a table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 17:20:34

first query already had them defined hence they didnt cause error


SELECT DISTINCT a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [smartview.exe],
d.Operating_System_Name_and0,
c.FilePath
FROM v_GS_SoftwareFile c INNER JOIN
v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = 'smartview.exe') 
ORDER BY a.Name0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-13 : 17:33:39

Got it...thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 17:43:46

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources