error in WHERE statement

dear sirs i am using sql server 2000 enterprise edition, i am new to sql server and also am learning the sql language...

I have a table named spt_datatype_info
that table has a column called TYPE_NAME
I have given a WHERE statement in a query:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

i know actually the value smallint has to be given in quotes...
Now my question is: When i give the Verify SQL syntax
then it does not return any error, but when i run it...then it given the following error...

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'smallint'.

what does this mean...??
If the SQL statement is wrong then it should return an error when verifying the statement...

regards,
kanishkit's because this sql is perfectly valid in terms of syntax:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

The only reason it fails is because there is no column named smallint in that table. That's not a syntax error.

Now this query, on the other hand, works, because both columns exist, and has exactly the same form as yours:

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = LOCAL_TYPE_NAME

is that what you are asking?|||not actually, i was saying that i was looking for a value in the column TYPE_NAME named smallint

I was giving the WHERE clause to look for a value called smallint in the column TYPE_NAME. the column has many vaules in it...smallint is one of them...

maybe i should have written it as:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

Since i am new to SQL language....what were you refering to...??...Although i quite got my answer...

Can you please tell me...what is the intersection of the column and row called??....for example in ms Excel when the row 7 and column D meet then a cell is formed...called D7

But in SQl server...what is the intersection of the Column and Row called...?? Like the cell in ms Excel...??

regards,
kanishk|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?|||the intersection of row and column is called "the value of column Y for row X"

:cool:

i realize that sounds somewhat flippant, but relational database theory is based upon primary keys, so "row X" means "the row where the primary key value is X" since that's how you tell rows apart, and "column Y" means "the column with 'Y' as the column name" since that's how you tell columns apart

simple, innit ;)|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?

thanks, i was wondering that when i give the command that you gave up...viz,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

when i run it, then automatically the TYPE_NAME column takes brackets,,,why is that??...i have shown it below...

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

regards,
kanishk|||That is because you are running this query in the SQL section of enterprise manager. Enterprise manager will try to bracket all where conditions
so if your query is something like

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint' OR TYPE_NAME = 'int'

Then on running it you will get

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint') OR (TYPE_NAME = 'int')

Try running the query in SQL Analyzer and you will not get the brackets..... however i guess the brackets are purely for readability and in the end finally makes no difference.....|||dear sir, i have started using the SQL query Analyzer, i have another question,
i just put in the LIKE condition...

SELECT * FROM spt_datatype_info WHERE TYPE_NAME LIKE '%s'

now this will give all the values in the TYPE_NAME column which start with any characters but end with 's' , thats why i have put '%s' , now suppose i want all the values that have 2 letters as starting and the 3rd letter as 's' , so what do symbol do i use??

Like in windows when we used to give in command prompt the ! , exclamation mark for the characters we did not know but were sure of the count of them, and the star '*' for the characters we did not know and were not sure of their count..

Similarly in SQL what would we give for the LIKE condition..??

regards,
kanishk|||... LIKE '__s%'this is all explained nicely in the manual