report syntax - 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 Programming

report syntax

Author

Topic

milagro
Starting Member

7 Posts

Posted - 2011-02-28 : 11:38:18

I have this report but get a syntax error when trying to get the ip address from the DeviceClientHealthState. Any help is appreciated.

select SYS.Netbios_Name0, IPAddresses, SF.FileName, SYS.User_Name0, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath
From v_GS_SoftwareFile SF join v_R_System SYS join v_DeviceClientHealthState
where SYS.ResourceID = SF.ResourceID
AND Netbios_Name = DeviceNetBiosName
Where SF.FileName LIKE 'desktop_client.exe'
ORDER BY SYS.Netbios_Name0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-28 : 12:18:52

You have two WHERE clauses in your statement.

btw.: You should use the correct JOIN syntax using ON rather then giving the join conditions in the WHERE clause

No, you're never too old to Yak'n'Roll if you're too young to die.

milagro
Starting Member

7 Posts

Posted - 2011-02-28 : 12:58:30

I was able to generate a report but the IP addresses did not come up. also I am not sure what you meant by "using ON rather then giving the join conditions in the WHERE clause"?
is the "The Language of SQL" a good reference tool for learning this language?

milagro
Starting Member

7 Posts

Posted - 2011-02-28 : 14:32:16

OK, so I changed the Join statement but now the IP.IPAddresses0 cannot be bound, statement follows

select SYS.Netbios_Name0, IP.IP_Addresses0, SF.FileName, SYS.User_Name0, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath
FROM v_R_System SYS join v_GS_SoftwareFile SF
ON SYS.ResourceID = SF.ResourceID
join v_RA_System_IPAddresses
ON SYS.ResourceID = SYS.ResourceID
AND SF.FileName LIKE 'desktop_client.exe'
ORDER BY SYS.Netbios_Name0

milagro
Starting Member

7 Posts

Posted - 2011-02-28 : 16:24:11

figured it out, you can consider this issue closed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources