Mr_Shaw
asked on
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
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
I doubt this could happen. Both the queries should result in the same output.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.
This is strange since id is an int.
Single quotes around the ID? Do you mean ID = '270727' ? In such case implicit conversion takes into effect.
ASKER
yes.
Strange. I know.
Any ideas why this has happened?
Strange. I know.
Any ideas why this has happened?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok.. I'll do some more investgation
ASKER
I ran sql profiler and the output looks excatly the same as the sql input/
So, what else you can do:
Upgrade the database engine and client software.
Upgrade the database engine and client software.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
[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?
ASKER
No I did not.
I just awarded points to multiple users.
I think something has gone wrong somewhere.
I just awarded points to multiple users.
I think something has gone wrong somewhere.
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...
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...
ASKER
What can i do about this?