error 102 on small query - 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 2008 Forums

Transact-SQL (2008)

error 102 on small query

Author

Topic

maravig
Starting Member

14 Posts

Posted - 2014-10-01 : 18:13:20

I'm relatively new to SQL. I'm trying to create a simple user based report and I'm getting an error 102 syntax error near '='. I've tried every thing I can think of. I'm sure it's the bottom =. Is it something obvious I'm overlooking?

Select vSMS_R_System.Distinguished_Name0, vSMS_R_System.AD_Site_Name0, vSMS_R_System.Resource_Domain_OR_Workgr0, v_R_User.Unique_User_Name0, v_R_User.Name0, v_R_User.department0,
v_R_User.manager0, vSMS_R_System.location0
FROM vSMS_R_System
JOIN v_R_User ON vSMS_R_System.Distinguished_Name0 = v_R_User.Distinguished_Name0
LEFT JOIN v_RA_System_System_Group_Name on vSMS_R_System.ResourceID = v_RA_System_System_Group_Name.ResourceID
Where v_RA_System_System_Group_Name.System_Group_Name0 in (select System_Group_Name0 from v_RA_System_System_Group_Name = 'CFG.FDROOT.NET/NDCS/HQF/Users' or
System_Group_Name0 from v_RA_System_System_Group_Name = 'CFG.FDROOT.NET/NDCS/DPF/Users' or System_Group_Name0 from v_RA_System_System_Group_Name = 'CFG.FDROOT.NET/NDCS/JXF/Users')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-01 : 18:19:14

Please show us the text of the error and not just the error number.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maravig
Starting Member

14 Posts

Posted - 2014-10-01 : 18:29:49

My apologies.

"An error occurred while executing the query.
Incorrect syntax near '='."
(Microsoft SQL Server, error 102)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-01 : 18:34:51

You've got a syntax error in the WHERE IN query: select System_Group_Name0 from v_RA_System_System_Group_Name = 'CFG.FDROOT.NET/NDCS/HQF/Users' or
System_Group_Name0 from v_RA_System_System_Group_Name

I think you copied/pasted into the wrong section or something. You are meaning to have a WHERE in there and instead have 3 FROMs.

I'm not sure why you are using a subquery there though. Can't you just do this:

Where v_RA_System_System_Group_Name.System_Group_Name0 in ('CFG.FDROOT.NET/NDCS/HQF/Users', 'CFG.FDROOT.NET/NDCS/DPF/Users', 'CFG.FDROOT.NET/NDCS/JXF/Users')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maravig
Starting Member

14 Posts

Posted - 2014-10-01 : 18:42:31

Thank you for the tip! Like I said, I'm new and learning. The query isn't pulling data yet, but no errors. I'll take that for today :-) Thanks again!

maravig
Starting Member

14 Posts

Posted - 2014-10-02 : 09:31:07

Well, after trying to solve this blank issue, I'm back. I suspect it's because the last part isn't the right view (Thanks to Tkizer for correcting my coding). So far, I've tried:

Where v_RA_System_System_Group_Name.System_Group_Name0 in ('CFG.FDROOT.NET/NDCS/HQF/Users', 'CFG.FDROOT.NET/NDCS/DPF/Users', 'CFG.FDROOT.NET/NDCS/JXF/Users')

Where v_R_UserGroup.Usergroup_Name0 in ('CFG.FDROOT.NET/NDCS/HQF/Users', 'CFG.FDROOT.NET/NDCS/DPF/Users', 'CFG.FDROOT.NET/NDCS/JXF/Users')

Is there a way to find out which SQL view is the correct one so data can be extracted properly? I mean I can keep looking for views with User_Group_Name0 or some variation, doesn't mean it's right.

maravig
Starting Member

14 Posts

Posted - 2014-10-02 : 12:06:24

UPDATE -
I decided to remove the bottom section as it should display the Usergroup_Name0 if specified like the AD_Site_Name0 field. I still get the headers and no data at all. I'm baffled. I can't understand why it's not pulling any data at all. Can anyone see what I'm doing wrong? Now the code looks like this:

Select vSMS_R_System.Distinguished_Name0, vSMS_R_System.AD_Site_Name0, vSMS_R_System.Resource_Domain_OR_Workgr0, v_R_System.User_Name0, v_R_User.Name0, v_R_User.employeenumber0, v_R_User.department0, v_R_User.l0, v_R_UserGroup.Usergroup_Name0
FROM v_R_User
JOIN vSMS_R_System ON v_R_User.Distinguished_Name0 = vSMS_R_System.Distinguished_Name0
INNER JOIN v_R_System ON v_R_User.Distinguished_Name0 = v_R_System.Distinguished_Name0
LEFT JOIN v_R_UserGroup on v_R_User.ResourceID = v_R_UserGroup.ResourceID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-02 : 12:53:00

This isn't something we can answer since you haven't provide enough info. Ideally we need to see sample data and expected output. We also can't tell you which view you need to use as we have no business knowledge of your system. You'll need to break the query down to determine where the issue is, like this:

select *
FROM v_R_User
JOIN vSMS_R_System ON v_R_User.Distinguished_Name0 = vSMS_R_System.Distinguished_Name0

Does that return data? If yes, then add the next join. Continue until no data shows, then you'll know which section needs to be changed or that you are calling the wrong view, or that there really is no data matching.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maravig
Starting Member

14 Posts

Posted - 2014-10-02 : 13:22:02

Ok, I'll start with that. Thank you for replying.

maravig
Starting Member

14 Posts

Posted - 2014-10-03 : 12:04:35

That was a good suggestion. I eventually found my way to
Select * from v_R_User and it had everything except one field. The powers that be want a column to know if the user has a desktop or laptop. I know this is the chassistype0 field from v_GS_SYSTEM_ENCLOSURE view. I'm not sure how to code this. I tried the following:

select *, v_GS_SYSTEM_ENCLOSURE.ChassisTypes0

FROM v_R_User

JOIN v_GS_SYSTEM_ENCLOSURE ON v_R_User.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID

It didn't like my syntax. This is going to be a one-off report so however I can get it done is fine :-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-03 : 12:07:13

Your syntax is fine. I checked it visually and then plopped it into SSMS and had it check. Are you testing things out in SSMS? Or some other tool? If it's some other tool, you'll want to get your query correct in SSMS first.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maravig
Starting Member

14 Posts

Posted - 2014-10-03 : 12:15:08

I was just trying to save it in Query Designer actually. The other thing I was thinking and not sure if this is possible. One of the fields is Machine Name. The 5th character determines if it's a Laptop or Desktop. Is it possible code a "If-Then" within the query and yield a new column with either the words "Laptop" or "Desktop"?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-03 : 13:15:55

Please use a regular query window and not the query designer. The query designer can't handle things except the most basic.

Yes you can get a new column: select ..., case when substring(somecolumn, 5, 1) = 'something' then 'Laptop' else 'Desktop' end as newcolumnname

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maravig
Starting Member

14 Posts

Posted - 2014-10-03 : 13:30:51

I dug a little further and the machine name was not in the User view. So I determined what I needed from the User view and wrote a select and that worked. I then added 2 fields as indicated below:


select v_R_User.Name0, v_R_User.User_Name0,
v_R_User.Distinguished_Name0, v_R_User.User_Principal_Name0,
v_R_User.Full_User_Name0,
v_R_User.department0, v_R_User.employeeNumber0, v_R_User.title0,
v_R_User.manager0, v_R_User.l0,
v_GS_SYSTEM_ENCLOSURE.Netbios_Name0,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0

FROM v_R_User

JOIN  v_GS_SYSTEM_ENCLOSURE ON v_R_User.User_Name0 =
v_GS_SYSTEM_ENCLOSURE.User_Name0

After adding the SYSTEM ENCLOSURE fields, I got this error:

An Error occurred while executing the query.
Invalid column name 'User_Name0',
Invalid column name 'Netbios_Name0' (Error 207)
How can I add an image?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-03 : 13:38:59

System enclosure doesn't contain those columns apparently.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maravig
Starting Member

14 Posts

Posted - 2014-10-03 : 13:41:50

I found the problem. Guess I have to find another view with chassistypes0 and netbiosname0 :-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-03 : 14:03:53

Don't just join to views because they have the column you want. The way the view is coded could mean your result set is bad. Instead, grab the columns from the tables. You can use the INFORMATION_SCHEMA.COLUMNS view to find where it is:

select *
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'the column name goes here'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maravig
Starting Member

14 Posts

Posted - 2014-10-04 : 16:34:26

Ok, I started by making a select for all the desired fields in v_R_User and that worked fine. I then added 2 fields from v_R_System and now I get nothing but headers. Here is the code:



select v_R_User.Name0, v_R_System.User_Name0,

v_R_User.Distinguished_Name0, v_R_User.User_Principal_Name0,

v_R_User.Full_User_Name0,

v_R_User.department0, v_R_User.employeeNumber0, v_R_User.title0,

v_R_User.manager0, v_R_User.l0, v_R_System.Netbios_Name0

FROM v_R_User

JOIN v_R_System ON v_R_User.ResourceID = v_R_System.ResourceID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-04 : 19:38:19

There must not be any matching rows between the two tables for the join condition. When you specify JOIN, it does an INNER JOIN which means you have to have matching rows for the join condition. If there are no matching rows, then nothing will show up.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources