CONVERT(INT, '1') = 1 --> 1 = 1 --> true 'A' = 1 --> CONVERT(INT, 'A') = 1 --> conversion error When you use an expression in a filter, e.g., WHERE varcharcol = 1 There's a difference between how SQL Server 7.0 and 2000 work. SQL Server 7.0 always converts the literal to the column's datatype to allow use of an index, so the expression becomes: WHERE varcharcol = CONVERT(VARCHAR, 1) However, SQL Server 2000 plays according to the datatype precedence rules and converts the lower to the higher, so the expression becomes: WHERE CONVERT(INT, varcharcol) = 1 If your column is indeed character based, I suspect two options: 1. If both are SQL Server 2000, all values in db1 are convertible to int, so you don't get any errors there. 2. If db1 is running SQL Server 7.0, the integer literals are converted to characters, so you don't get any errors. At any rate, if the column is character based, the literals should be specified as character ones. That's the safest way to go, which would also allow the use of an index. -- BG, SQL Server MVP Solid Quality Learning [url]www.solidqualitylearning.com[/url] "rodger" wrote in message news:350901c3400e$f0607940$a601280a@phx.gbl...[quote] > when i type the following query in db1 i get the error > mentioned below > > select * from atsymcatransmittals > where chvTransmittalNo in > (249161, 249162, 249163, 249164) > order by guiuniqueid > > they produce the following error when run in SqlTes03.yrs: > > Server: Msg 245, Level 16, State 1, Line 1 > > Syntax error converting the varchar > value '00000020030531RIIN ' to a column of data type int. > > but if i give the same query on a similar database db2 it > gives me results > > if i give the search condition in single quotes in db1 it > gives me results, but still the case should be similar for > the other database db2 whih is exactly the same. any > database options which i am mising in db1 > > thanks > > rodger > > >[/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => <350901c3400e$f0607940$a601280a@phx.gbl> [htmlstate] => on_nl2br [postusername] => Itzik Ben-Gan [ip] => REMOVETHIS.itzi [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 4 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> simple query.... - Microsoft SQL / MS SQL Server

simple query.... - Microsoft SQL / MS SQL Server

when i type the following query in db1 i get the error mentioned below select * from atsymcatransmittals where chvTransmittalNo in (249161, 249162, 249163, 249164) order by guiuniqueid they produce the following error when run in SqlTes03.yrs: Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value '00000020030531RIIN ' to a column of data type int. but if i give the same query on a similar database db2 it gives me results if i give the search condition in single quotes in db1 it gives me results, but still the case should be similar for ...

  1. #1

    Default simple query....

    when i type the following query in db1 i get the error
    mentioned below

    select * from atsymcatransmittals
    where chvTransmittalNo in
    (249161, 249162, 249163, 249164)
    order by guiuniqueid

    they produce the following error when run in SqlTes03.yrs:

    Server: Msg 245, Level 16, State 1, Line 1

    Syntax error converting the varchar
    value '00000020030531RIIN ' to a column of data type int.

    but if i give the same query on a similar database db2 it
    gives me results

    if i give the search condition in single quotes in db1 it
    gives me results, but still the case should be similar for
    the other database db2 whih is exactly the same. any
    database options which i am mising in db1

    thanks

    rodger



    rodger Guest

  2. #2

    Default Re: simple query....

    since chvTransmittalNo is a varchar column you must always use single
    quotes while comparing.

    so your query should be

    select * from atsymcatransmittals
    > where chvTransmittalNo in
    > ('249161', '249162', '249163', '249164')

    "rodger" <lopesymcaret.org> wrote in message
    news:350901c3400e$f0607940$a601280aphx.gbl...
    > when i type the following query in db1 i get the error
    > mentioned below
    >
    > select * from atsymcatransmittals
    > where chvTransmittalNo in
    > (249161, 249162, 249163, 249164)
    > order by guiuniqueid
    >
    > they produce the following error when run in SqlTes03.yrs:
    >
    > Server: Msg 245, Level 16, State 1, Line 1
    >
    > Syntax error converting the varchar
    > value '00000020030531RIIN ' to a column of data type int.
    >
    > but if i give the same query on a similar database db2 it
    > gives me results
    >
    > if i give the search condition in single quotes in db1 it
    > gives me results, but still the case should be similar for
    > the other database db2 whih is exactly the same. any
    > database options which i am mising in db1
    >
    > thanks
    >
    > rodger
    >
    >
    >

    Partha Mandayam Guest

  3. #3

    Default simple query....

    I am guessing that chvTransmittalNo is a character field.
    Try putting single quotes around the list of values.

    >-----Original Message-----
    >when i type the following query in db1 i get the error
    >mentioned below
    >
    >select * from atsymcatransmittals
    >where chvTransmittalNo in
    >(249161, 249162, 249163, 249164)
    >order by guiuniqueid
    >
    >they produce the following error when run in SqlTes03.yrs:
    >
    >Server: Msg 245, Level 16, State 1, Line 1
    >
    >Syntax error converting the varchar
    >value '00000020030531RIIN ' to a column of data type int.
    >
    >but if i give the same query on a similar database db2 it
    >gives me results
    >
    >if i give the search condition in single quotes in db1 it
    >gives me results, but still the case should be similar
    for
    >the other database db2 whih is exactly the same. any
    >database options which i am mising in db1
    >
    >thanks
    >
    >rodger
    >
    >
    >
    >.
    >
    DJoe Guest

  4. #4

    Default Re: simple query....

    When you write an expression with operands of different datatypes, generally speaking, SQL Server converts the lower precedence datatype to the higher one.
    For example:

    '1' = 1 --> CONVERT(INT, '1') = 1 --> 1 = 1 --> true
    'A' = 1 --> CONVERT(INT, 'A') = 1 --> conversion error

    When you use an expression in a filter, e.g.,

    WHERE varcharcol = 1

    There's a difference between how SQL Server 7.0 and 2000 work. SQL Server 7.0 always converts the literal to the column's datatype to allow use of an index, so the expression becomes:

    WHERE varcharcol = CONVERT(VARCHAR, 1)

    However, SQL Server 2000 plays according to the datatype precedence rules and converts the lower to the higher, so the expression becomes:

    WHERE CONVERT(INT, varcharcol) = 1

    If your column is indeed character based, I suspect two options:

    1. If both are SQL Server 2000, all values in db1 are convertible to int, so you don't get any errors there.
    2. If db1 is running SQL Server 7.0, the integer literals are converted to characters, so you don't get any errors.

    At any rate, if the column is character based, the literals should be specified as character ones. That's the safest way to go, which would also allow the use of an index.

    --
    BG, SQL Server MVP
    Solid Quality Learning
    [url]www.solidqualitylearning.com[/url]


    "rodger" <lopesymcaret.org> wrote in message news:350901c3400e$f0607940$a601280aphx.gbl...
    > when i type the following query in db1 i get the error
    > mentioned below
    >
    > select * from atsymcatransmittals
    > where chvTransmittalNo in
    > (249161, 249162, 249163, 249164)
    > order by guiuniqueid
    >
    > they produce the following error when run in SqlTes03.yrs:
    >
    > Server: Msg 245, Level 16, State 1, Line 1
    >
    > Syntax error converting the varchar
    > value '00000020030531RIIN ' to a column of data type int.
    >
    > but if i give the same query on a similar database db2 it
    > gives me results
    >
    > if i give the search condition in single quotes in db1 it
    > gives me results, but still the case should be similar for
    > the other database db2 whih is exactly the same. any
    > database options which i am mising in db1
    >
    > thanks
    >
    > rodger
    >
    >
    >
    Itzik Ben-Gan Guest

  5. #5

    Default Re: simple query....


    both the databases db1 and db2 are same, so it works
    without quotes in db2 but not in db1.

    It works fine with quotes on both the databases.

    But the question is why it is not showing me results in db1

    I agree it should be in quotes...'

    any idea?????

    >-----Original Message-----
    >since chvTransmittalNo is a varchar column you must
    always use single
    >quotes while comparing.
    >
    >so your query should be
    >
    >select * from atsymcatransmittals
    >> where chvTransmittalNo in
    >> ('249161', '249162', '249163', '249164')
    >
    >
    >"rodger" <lopesymcaret.org> wrote in message
    >news:350901c3400e$f0607940$a601280aphx.gbl...
    >> when i type the following query in db1 i get the error
    >> mentioned below
    >>
    >> select * from atsymcatransmittals
    >> where chvTransmittalNo in
    >> (249161, 249162, 249163, 249164)
    >> order by guiuniqueid
    >>
    >> they produce the following error when run in
    SqlTes03.yrs:
    >>
    >> Server: Msg 245, Level 16, State 1, Line 1
    >>
    >> Syntax error converting the varchar
    >> value '00000020030531RIIN ' to a column of data type
    int.
    >>
    >> but if i give the same query on a similar database db2
    it
    >> gives me results
    >>
    >> if i give the search condition in single quotes in db1
    it
    >> gives me results, but still the case should be similar
    for
    >> the other database db2 whih is exactly the same. any
    >> database options which i am mising in db1
    >>
    >> thanks
    >>
    >> rodger
    >>
    >>
    >>
    >
    >
    >.
    >
    rodger Guest

  6. #6

    Default Re: simple query....

    Have you checked for the value '00000020030531RIIN' in
    each database? Does it exist?

    >-----Original Message-----
    >
    >both the databases db1 and db2 are same, so it works
    >without quotes in db2 but not in db1.
    >
    >It works fine with quotes on both the databases.
    >
    >But the question is why it is not showing me results in
    db1
    >
    >I agree it should be in quotes...'
    >
    >any idea?????
    >
    >
    >>-----Original Message-----
    >>since chvTransmittalNo is a varchar column you must
    >always use single
    >>quotes while comparing.
    >>
    >>so your query should be
    >>
    >>select * from atsymcatransmittals
    >>> where chvTransmittalNo in
    >>> ('249161', '249162', '249163', '249164')
    >>
    >>
    >>"rodger" <lopesymcaret.org> wrote in message
    >>news:350901c3400e$f0607940$a601280aphx.gbl...
    >>> when i type the following query in db1 i get the error
    >>> mentioned below
    >>>
    >>> select * from atsymcatransmittals
    >>> where chvTransmittalNo in
    >>> (249161, 249162, 249163, 249164)
    >>> order by guiuniqueid
    >>>
    >>> they produce the following error when run in
    >SqlTes03.yrs:
    >>>
    >>> Server: Msg 245, Level 16, State 1, Line 1
    >>>
    >>> Syntax error converting the varchar
    >>> value '00000020030531RIIN ' to a column of data type
    >int.
    >>>
    >>> but if i give the same query on a similar database db2
    >it
    >>> gives me results
    >>>
    >>> if i give the search condition in single quotes in db1
    >it
    >>> gives me results, but still the case should be similar
    >for
    >>> the other database db2 whih is exactly the same. any
    >>> database options which i am mising in db1
    >>>
    >>> thanks
    >>>
    >>> rodger
    >>>
    >>>
    >>>
    >>
    >>
    >>.
    >>
    >.
    >
    DJoe Guest

  7. #7

    Default Re: simple query....


    Problem is with the values, one database has the value the
    second does not have the value....

    Thanks a Lot guys.

    Rodger.

    >-----Original Message-----
    >Have you checked for the value '00000020030531RIIN' in
    >each database? Does it exist?
    >
    Rodger Guest

Similar Threads

  1. Simple query, very low performance
    By Bernhard Kornberger in forum MySQL
    Replies: 3
    Last Post: July 6th, 12:27 PM
  2. simple (?) query--help
    By Bosconian in forum MySQL
    Replies: 2
    Last Post: December 20th, 12:43 AM
  3. (simple?) query dilemma--help please
    By Bosconian in forum MySQL
    Replies: 2
    Last Post: November 19th, 02:00 AM
  4. Simple Query for Search
    By amystephenson in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: May 12th, 01:13 PM
  5. [PHP] Simple forms query
    By Matt Matijevich in forum PHP Development
    Replies: 2
    Last Post: July 9th, 04:50 PM

Bookmarks

Posting Permissions

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