Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    15

    Unanswered: 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,
    kanishk

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it'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?

  3. #3
    Join Date
    Apr 2007
    Posts
    15

    not actually

    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

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the intersection of row and column is called "the value of column Y for row X"




    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2007
    Posts
    15

    brackets...

    Quote Originally Posted by jezemine
    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

  7. #7
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    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......

  8. #8
    Join Date
    Apr 2007
    Posts
    15

    the LIKE condition

    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ... LIKE '__s%'
    this is all explained nicely in the manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •