SQL int value in where.

Here is my query (angelIII helped me)

 SELECT *
  FROM ( SELECT ID, BegDate, Zip,HP_Type
              , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY BegDate ASC) rn
           FROM [Add]
       ) sq
 WHERE sq.RN = 1 and  HP_Type='SN' and ID =270727

When I run this it does not return any results.

However when I add a space between the '=' and ID for example ID = 270727 it works

I can't understand this. The ID is an Int
Mr_ShawAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Mr_ShawConnect With a Mentor Author Commented:
I have wrapped the code inside a view. It seems to have done the job. Just test it a little.

0
 
Mr_ShawAuthor Commented:
I have tested my theory and some id's need the extra space and some do not.

What can  i do about this?
0
 
sameer2010Commented:
I doubt this could happen. Both the queries should result in the same output.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pcelbaCommented:
Sounds like a nonsense... Space at this place has no meaning at all.

You should tell what SQL Server version and what client software are you using. Also some printscreens of your data would help.

You should use SQL Profiler and look at the query sent to the backend.
0
 
Daniel WilsonConnect With a Mentor Commented:
can you give us the output of

sp_help [Add]
0
 
Mr_ShawAuthor Commented:
Hi sameer2010 and pcelba,

What I am saying really does happen. I can't believe it myself.

I ran sp_help and the results returned for the ID column are:

Column_name: ID
Type:      int
Computed:      no
Length:      4
Prec:      10
Scale:      0
Nullable:      no
TrimTrailingBlanks:      (n/a)
FixedLenNullInSource:      (n/a)
Collation:      Null

I am not sure how to interpret the Prec = 10
0
 
Mr_ShawAuthor Commented:
I found that if i put single quotes marks around the ID the query also brings back results.

This is strange since id is an int.
0
 
pcelbaCommented:
Single quotes around the ID? Do you mean  ID = '270727' ? In such case implicit conversion takes into effect.
0
 
Mr_ShawAuthor Commented:
yes.

Strange. I know.

Any ideas why this has happened?
0
 
pcelbaConnect With a Mentor Commented:
Length:    4  ... occupies 4 bytes
Prec:      10  ... max 10 digits
Scale:      0  ... no decimal places

Some invalid interpretation of your query is still possible.
You should tell what SQL Server version and what client software are you using.
You should use SQL Profiler and look at the query sent to the backend.
0
 
Mr_ShawAuthor Commented:
ok.. I'll do some more investgation
0
 
Mr_ShawAuthor Commented:
I ran sql profiler and the output looks excatly the same as the sql input/
0
 
pcelbaCommented:
So, what else you can do:

Upgrade the database engine and client software.
0
 
Mr_ShawAuthor Commented:
OK. I tested it and it did not work.   But I have found something which you might be able to tell me what is happening.

[a] is the view.

When I run SELECT  * from dbo.[a] where EntSys =35931 nothing happens.

If I immediately run it again I get a row.

If I then immediately run it again I get nothing and so on.

I don't understand it at all. Is it becuase i have a PARTITION in the code?


0
 
Mr_ShawAuthor Commented:
No I did not.

I just awarded points to multiple users.

I think something has gone wrong somewhere.
0
 
pcelbaCommented:
The question was not closed because you selected one of your posts as the Accepted solution among others. Never maind, admins will close it.

I have been trying to simulate your behavior on SQL 2008 (ver. 10.0.2531) and SQL 2005 Express (ver. 9.0.4053) but without problems. I've been trying several scenarios. The only problematic was hard space but it reported Syntax error.

I've been also trying ODBC connection. Again without problems.

PARTITION is used to generate the row numbers and it should not affect WHERE condition. (I've used it)

I really don't know what's happening here. But (not only) MS software can behave this way sometimes and updates are confidential about fixed bugs in many cases...

0
All Courses

From novice to tech pro — start learning today.