asked
isnull conversion fail
i have a where clause which is throwing me error conversion failed when converting the vachar value "KL" to data type int select * from student st where ISNULL(st.rollno, '') NOT IN (2001,2003) wht could be the alternavite stmt?
This happens not because the **isnull** fails but rather because **NOT IN** does. You instruct the st.rollno value to be equal to empty string if such value is null. This part works fine. The problem is that the datatype of your column is varchar but you try to compare it to numeric values, such as 2001 and 2003. For you query to work, you will need to wrap those numbers into the single quotes: select * from student st where ISNULL(st.rollno, '') NOT IN ('2001', '2003'); Oleg
@Usman that would give different results. If there is a row where rollno is null, then the first query will return it as the null is converted to an empty string then that is compared to the IN list, it is not in the list so the predicate is TRUE. With your query you are comparing a null to the IN list, which will result in FALSE(well actually result in UNKNOWN, but that still isn't TRUE). Try it : if object_id('student','U') is not null drop table student go create table student(rollno varchar(10)) go insert into student(rollno) select 2000 insert into student(rollno) select 2001 insert into student(rollno) select 2002 insert into student(rollno) select 2003 insert into student(rollno) select 'KL' insert into student(rollno) select null select * from student st where isnull(rollno, '') not in ('2001','2003') -- gives 4 rows select * from student st where rollno not in ('2001','2003') -- gives 3 rows
@palum Why complicating the query and reduce the performance by using ISNULL? When a simple WHERE clause could be WHERE rollno NOT IN ( '2001', '2003' ) OR rollno IS NULL OR WHERE NOT EXISTS ( SELECT [Literals].[RollNo] FROM ( SELECT '2001' RollNo UNION ALL SELECT '2003' ) Literals WHERE Literals.RollNo = st.rollno )
@Kev Riley +1 and thanks for reminding the typical NOT IN and NULL scenario. This is one of the reasons I always prefer EXISTS. But still that could be handled in a better way as edited in my response. Thanks once again.
3 People are following this question.
