May 5, 2015 at 4:30 pm
Hello comunity
I found a code snippet that use the curious following sintax on the creation of View:
CREATE VIEW [dbo].[vw_EvenValues]
AS
SELECT [TestColumn]
FROM [dbo].[TestTable]
WHERE [TestColumn] % 2 = 1
I don´t understand this:
WHERE [TestColumn] [highlight=#ffff11]% 2 [/highlight]= 1
IF [TestColumn] on the Select is varchar, then error occurs and say:
"Conversion failed when converting for the varchar value 'A001' to data type int"
Someone could explain what thas it´s mean.
Best regards,
Luis Santos
May 5, 2015 at 4:33 pm
you might wish to get familiar with arithmetic operators used in T-SQL:
https://msdn.microsoft.com/en-us/library/ms187716.aspx
_____________
Code for TallyGenerator
May 5, 2015 at 4:48 pm
Hello Sergiy
Thanks for your reply, on arithmetic operators i don´t have dought, because if i select a numeric field everything done.
But if i choose a varchar field, this snippet don´t work, or i´am wrong ?
If i wrong could post a very simple select with one varchar field ?
Thanks,
Luis Santos
May 5, 2015 at 5:09 pm
The where clause is figuring out if the remainder when you divide a number by 2 is equal to 1. % is known as the modulo operator.
As a result, it can only be applied to a number. If you apply it to a varchar column it will try to cast to an integer. If there are any non numeric numbers in the varchar column that will fail.
May 5, 2015 at 9:02 pm
Can only agree with Nevin.
When you apply this operator to a varchar field you force an implicit conversion which may fail if a value cannot be converted to int.
That's where your error message comes from.
_____________
Code for TallyGenerator
May 6, 2015 at 7:36 am
To get an 'Conversion failed..' error raised is quite enough to have[testcolumn] - 0 at select . Why to put it as [testcolumn] %2 =1 at where ? So a programer had some other intentions in mind. Maybe.
May 6, 2015 at 10:04 am
luissantos (5/5/2015)
Hello comunityI found a code snippet that use the curious following sintax on the creation of View:
CREATE VIEW [dbo].[vw_EvenValues]
AS
SELECT [TestColumn]
FROM [dbo].[TestTable]
WHERE [TestColumn] % 2 = 1
I don´t understand this:
WHERE [TestColumn] [highlight=#ffff11]% 2 [/highlight]= 1
IF [TestColumn] on the Select is varchar, then error occurs and say:
"Conversion failed when converting for the varchar value 'A001' to data type int"
Someone could explain what thas it´s mean.
Best regards,
Luis Santos
Looks to me that the name of the view is wrong, CREATE VIEW [dbo].[vw_EvenValues]. The view will return ODD values, not even.
For even values in TestColumn the where clause should be this:
WHERE TestColumn % 2 = 0
May 6, 2015 at 10:13 am
In addition to what everyone else has said...
Someone could explain what thas it´s mean.
On a performance note it means that you're going to get an index scan instead of a seek.
-- Itzik Ben-Gan 2001
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply