March 9, 2011 at 9:37 am
I have just downloaded the adventureworks sample db and was trying to run a few queries. my select works fine but when I add a where clause - using the same column name as in the select - I always get an invalid column name error.
I downloaded the db, attached it and tried to run the queries. Is there something else that I should have done?
I'm by no means a sql expert but I have done a select with a where clause successfully.
thanks
March 9, 2011 at 10:15 am
It could be a number of things, but Where clauses do work with AdventureWorks
If you can post the query then someone should be able to spot the issue
March 9, 2011 at 11:01 am
Sounds like you're not table qualifying the columns in the WHERE clause. Add the table and you should be fine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2011 at 11:34 am
Do you mean by qualifying, specifying the db and table names? if so, I've tried that and still get the error.
Here is the query:
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[ReorderPoint]
,[ListPrice]
,[ProductLine]
,[Class]
,[Style]
FROM [AdventureWorks].[Production].[Product]
where [AdventureWorks].[Production].[Product]. like 'black%'
March 9, 2011 at 12:11 pm
ksharpe (3/9/2011)
Do you mean by qualifying, specifying the db and table names? if so, I've tried that and still get the error.Here is the query:
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[ReorderPoint]
,[ListPrice]
,[ProductLine]
,[Class]
,[Style]
FROM [AdventureWorks].[Production].[Product]
where [AdventureWorks].[Production].[Product]. like 'black%'
It's working fine like this:
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[ReorderPoint]
,[ListPrice]
,[ProductLine]
,[Class]
,[Style]
FROM [AdventureWorks].[Production].[Product]
where like 'black%'
here's how I'd recommend you write the code though:
SELECT p.[ProductID]
,p.[Name]
,p.[ProductNumber]
,p.[Color]
,p.[ReorderPoint]
,p.[ListPrice]
,p.[ProductLine]
,p.[Class]
,p.[Style]
FROM [Production].[Product] AS p
where p. like 'black%'
Are you sure you don't have a second table involved in a join or something?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2011 at 12:15 pm
Try using a capital 'C' in the where clause the for color column name
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[ReorderPoint]
,[ListPrice]
,[ProductLine]
,[Class]
,[Style]
FROM [AdventureWorks].[Production].[Product]
where [AdventureWorks].[Production].[Product].[Color] like 'black%'
It would appear that you have the database set to use a case sensitive collation
http://msdn.microsoft.com/en-us/library/ms143726%28SQL.90%29.aspx"> http://msdn.microsoft.com/en-us/library/ms143726%28SQL.90%29.aspx
you can find out what the collation is set to by right-clicking the database name and selecting properties
also you can change the collation of the database, or just use the correct case i
March 9, 2011 at 2:10 pm
Steve and Grant
Thank you both so much for your replies - and so promptly!
It does appear that I'm using the case sensitive collation as changing both the 'c' in color and the 'b' in black to uppercase, solved the problem - and I thought that I had tried copying the field name from the select statement to try to get it to run but I guess not.
I think that I shall remember that issue for a while and not get caught again.
I don't know how you do it - I can barely get my work done in a day - but I'm glad that you do.
Cheers
Kim
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply