November 21, 2011 at 9:52 am
I am having the darndest time selecting rows where a column value is <=9999.
Here is the schema for the View(dbo.designations) I am working with:
designationname (varchar(150), null)
accountcode (bigint, null)
My query:
select *
from dbo.designations where accountcode<=9999
Error message:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
I tried casting the 9999 and/or the accountcode to a bigint in there where statement to no avail.
What is wierd is that I can compare accountcode to 9999 in the select statement like so:
select designationname, case when accountcode<9999 then accountcode else '' end as NewBookNumber
from dbo.designations
Any help would be greatly appreciated!
November 21, 2011 at 10:44 am
To be honest with you and to check my thoughts I performed the following:
CREATE TABLE #desigations(
designationname varchar(150),
accountcode bigint)
INSERT INTO #desigations(designationname,accountcode)
SELECT NULL,77 UNION ALL
SELECT 'Less',8 UNION ALL
SELECT 'Something',9999 UNION ALL
SELECT 'nothing',NULL
select * from #desigations where accountcode<=9999
Results:
designationnameaccountcode
Something 9999
Less 8
NULL 77
select designationname, case when accountcode<9999 then accountcode else '' end as NewBookNumber
from #desigations
Results:
designationnameNewBookNumber
Something 0
nothing 0
Less 8
NULL 77
Can you supply some sample data, as my results of course depend upon my input. And note the difference <=9999 vs <9999 in your 2 SELECT statements
November 21, 2011 at 3:30 pm
The problem here is actually "an old friend". You can rely on implicit cast if you know how it works. If not, you should always use explicit cast. Actually, I tend to prefer explicit cast anyway.
When you compare a varchar and an integer of some kind, the varchar is converted to the integer type, not the other way around. This has a few consequences: SQL Server will not be able to seek an index on a varchar column to find matching rows using an integer as filter, and more critically: Every single non-null value in the varchar field must be the text representation of an int, otherwise you get the value described in the top post.
November 21, 2011 at 4:12 pm
Hi Ron,
I follow your logic perfectly and expect the same results. Could this issue have to do with the fact that I am querying a view? In the origional table, the accountcode is stored as a varchar. Then I created two views, one with numeric values for the accountcode and one with non-numeric values using the isnumeric() function. In my previous post, I am only querying the numeric view so there is no way that I have non-numeric values. Here is the ddl for my base table from which my numeric view queries:
dbo.BaseTable
designation (varchar(50), null)
accountcode (varchar(50), null)
Here is the sql for my dbo.designations view (the numeric view):
SELECT DesignationName, cast(AccountCode AS bigint) AS AccountCode
FROM dbo.BaseTable
WHERE ISNUMERIC(AccountCode) = 1
Sample data is as follows:
The Childrens Trust, 4039587349
Habitat for Humanity, 3923892839
St. Andrews Church, 2394939929
November 21, 2011 at 6:36 pm
hkflight (11/21/2011)
Hi Ron,I follow your logic perfectly and expect the same results. Could this issue have to do with the fact that I am querying a view?
Once again I have converted your code, with a few minor corrections (namely in the CREATE TABLE Dbo.BaseTable)
Ran this code:
INSERT INTO BaseTable
SELECT 'The Childrens Trust', 4039587349 UNION ALL
SELECT 'Habitat for Humanity', 3923892839 UNION ALL
SELECT 'St. Andrews Church', 2394939929
CREATE VIEW dbo.designations
AS
SELECT Designation, cast(AccountCode AS bigint) AS AccountCode
FROM BaseTable WHERE ISNUMERIC(AccountCode) = 1
select * from designations where accountcode<=3923892839
Result:
DesignationAccountCode
Habitat for Humanity3923892839
St. Andrews Church2394939929
My thought is the portion of the view statement, that is WHERE ISNUMERIC(AccountCode) = 1, is not performing as you believe it should.
I will refer you to an article by Jeff Moden, in the "Spackle series of articles" here on SCC.
http://www.sqlservercentral.com/articles/IsNumeric/71512/
For the lack of any definitive data, which might contain an error, it is difficult to know of a solution. But Jeff's article will give you the T-SQL code to do the conversion of a VARCHAR value to a BIGINT, and I would suggest that you utilize Jeff's code in creating your view.
November 21, 2011 at 7:08 pm
hkflight (11/21/2011)
Hi Ron,I follow your logic perfectly and expect the same results. Could this issue have to do with the fact that I am querying a view? In the origional table, the accountcode is stored as a varchar.
This answers your question..
===============================================================
"lets do amazing" our company motto..
November 21, 2011 at 7:56 pm
bitbucket-25253 (11/21/2011)
I will refer you to an article by Jeff Moden, in the "Spackle series of articles" here on SCC.
Heh... I just knew that article would come in handy. I just didn't expect you to beat me to the punch on it. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply