none
Why does comparison of a where condition to a numeric value without quotations(char datatype) returns wrong records? RRS feed

  • Dotaz

  • Hi,

    I have this table(3rd pic).....with data as follows(1 st pic)...

    My Question is when I try this below query(where [colname]=0)..two rows are coming up...but if i try (where [colname]='0')( with quotes).Only one row is showing up.can I know the reason behind it please?

    Thank u


    pondělí 21. října 2019 14:49

Odpovědi

Všechny reakce

  • "-" is the same as "-0". And as you're querying for 0 a type conversion happens. Thus two rows.

    p.s. from the given sample, your travel code column seems to make no sense. It should be NULL or 1..2. But nothing else. From the semantics, code and key seem to be also redundant.

    pondělí 21. října 2019 14:57
  • Just to be clear.

    This is because of "implicit conversion" of the TravelModeCode to an integer value.  Your query results in:

    …
    WHERE CAST([TravelModeCode] AS INT) = 0
    

    It is lucky all your values for TravelModeCode are able to be converted by implict conversion to integer and SQL Server converts '-' to 0 and does not error.  If you had other non-integer values, you would be getting an error "cannot convert 'a' to integer".

    You can see this by running:

    SELECT CAST([TravelModeCode] AS INT) as Int_TravelModeCode
    FROM ....
    

    Please see:

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver15#implicit-and-explicit-conversion

    • Označen jako odpověď Jennifer Zen pondělí 21. října 2019 17:57
    pondělí 21. října 2019 15:32
    Moderátor
  • Hi,

    Thank you very much for the explanation and also after going through the article ,I was able to solve the issue with my stored proc by explicity casting the above field as varchar.

    Thanks

    Jenni


    pondělí 21. října 2019 18:04
  • Hi,

    Thanks for your reply.

    pondělí 21. října 2019 18:05
  • Numeric values do not have quote marks!! Where did you get this idea? Also, please stop posting pictures like a child who does not have words yet. Where is the DDL? Sample data? Then why did you use integer for a code? Codes are on a nominal scale, and not a ratio or interval scale. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    pondělí 21. října 2019 19:37
  • Sir,
    Please calm down....

    --Numeric values do not have quote marks!! --
    I know numeric values don't have quotations-Read my question properly

    --Where did you get this idea? ---
    Ever heard of curiosity to test when you are learning something?? If you see ,the field is char datatype (from pics)

    ---Also, please stop posting pictures like a child who does not have words yet---
    Ever heard of this"Pictures Speak Louder Than Words"
    Looks like you are an author,u should have known better.

    --Where is the DDL? Sample data? Then why did you use integer for a code?--
    Cant u see the pics??

    --- Codes are on a nominal scale, and not a ratio or interval scale---
    I was given the dataset to work on..That's how it is...
    I already marked Tom Phillips's reply as an answer and I very much appreciate his help for explaining about sql conversions.
    People come here desperate to find an answer for something that they couldn't get on google search.
    Please do bear in mind that a person can be just a beginner and naive and trying to learn something..So rather than replying with all sort of weird questions..please help like others and if not just move on..
    No need to be rude ..

    Thanks,
    Jenni


    úterý 22. října 2019 17:29
  • Good day Jennifer,

    I recommend to ignore CELKO as others do.
    He is a well know abuser who is bullying newbies IMO probably in order to show that he is better. I notice that he try to pick irrelevant points instead of providing answer to the question many times (I wonder if he know the answer in these cases).

    You can check the following thread where he complained that the spam engine marked his messages as spam. He did not even have the minimum honor of reviewing the responses he received and answer them or closing the thread, which is probably one of the most rude actions someone can do in the forum! I hate it when someone open a thread and do not come back to it.

    Here you can see his "ability" to write code when he actually try to provide answer:
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f1b3ad8f-7936-419b-b10d-8e2c19e4d266/aggregate-max-on-sub-query-with-quottop-nquot-is-not-working-correctly?forum=transactsql#f308e0cf-1b70-49c6-9909-c99d9faeaa9d

    This simple and so short code which he provided includes multiple mistakes and it will raise error if you will try to execute it. For example (1) using ROW_Number as it was a variable instead of function (2) using where condition on "t" while t is not the column's name but alias (3) Using where condition on row_number function (issue 2 and 3 could be avoid by using CTE)...

    CREATE VIEW Top_three_foobar (foobar, foobar_top)
    AS 
    (SELECT foobar, ROW_NUMBER OVER (ORDER BY foobar DESC) AS t
       FROM Floob
    WHERE t <= 3);
    In short, I recommend simply to ignore and not to open a fight in the forum😀

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    středa 23. října 2019 5:57
    Moderátor
  • Hi Ronen,

    Thank you very much for your kind and supporting response. I really appreciate people like you, who takes time to help us naive people in understanding the problem. I assure you, I never want to pick up a fight with anyone on internet, let alone this forum. :)

    It really feels bad when someone is being rude to you when your learning something.It creates a fear to ask another question hoping that it can be a dumb one and someone can get offended by it.

    I pray for the person who gets him as a senior/manager..

    Regarding his query,he could have also used that select statement as a subquery to get the records from and putting that where clause out of the window .(Ofcourse treating partition as a function)

    But anyways thank very much for your response.I appreciate it :)

    Thanks

    Jenni


    středa 23. října 2019 15:16