March 6, 2008 at 7:16 am
I've run into a problem with a NOT IN condition that uses a subquery.
I have 2 tables, a data table and an exclusions table.
I want to return all the rows from the data table where the value of a particular BIGINT column does not exist in the exclusions table.
ExclusionsTable:
appname varchar
type varchar
subtype varchar
value varchar
my select statement:
SELECT *
FROM datatable
WHERE BigIntCol NOT IN
(
SELECT CAST(value AS BIGINT)
FROM ExclusionsTable
WHERE appname = 'abc'
AND type = 'def'
AND subtype = 'ghi'
)
All of the values for the value column on rows that match the given appname/type/subtype in the exclusions table are numeric and easily fit into a bigint. However, there are non-numeric values in that column for other appname/type/subtype records.
The subselect executes fine if I execute it as a standalone. However, when the entire query is run as a unit I get "Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint"
I stumped for a work-around.
Any ideas?
Thanks
John Woods
March 6, 2008 at 9:00 am
Don't use an IN/NOT IN clause, use a LEFT JOIN instead and throw the CAST on the BigIntColumn in the JOIN as follows:
--CREATE TABLES
CREATE TABLE datatable (BigIntCol bigint)
CREATE TABLE ExclusionsTable (appname varchar(30), type varchar(30), subtype varchar(30), value varchar(30))
--INSERT RECORDS
INSERT INTO datatable(BigIntCol)
VALUES(12345678900)
INSERT INTO datatable(BigIntCol)
VALUES(1234567890011)
INSERT INTO datatable(BigIntCol)
VALUES(1234567890022)
INSERT INTO ExclusionsTable(appname, type, subtype, value)
VALUES('abc', 'def', 'ghi', 'A12345678900')
INSERT INTO ExclusionsTable(appname, type, subtype, value)
VALUES('abc', 'def', 'ghi', 'A1234567890011')
INSERT INTO ExclusionsTable(appname, type, subtype, value)
VALUES('abc', 'def', 'ghi', '1234567890011')
INSERT INTO ExclusionsTable(appname, type, subtype, value)
VALUES('bad', 'bad', 'bad', '12345678900')
INSERT INTO ExclusionsTable(appname, type, subtype, value)
VALUES('bad', 'bad', 'bad', 'A12345678900')
--SHOW INSERTED DATA
SELECT * FROM datatable
SELECT * FROM ExclusionsTable
--RUN LEFT OUTER JOIN
SELECT DT.*
FROM datatable DT LEFT JOIN ExclusionsTable X ON CAST(DT.BigIntCol AS varchar(30)) = X.value
WHERE X.value IS NOT NULL
AND X.appname = 'abc'
AND X.type = 'def'
AND X.subtype = 'ghi'
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 6, 2008 at 9:00 am
Hi John,
I have executed the same from my side .I am not at all facing any problem.see the code
CREATE TABLE [dbo].[EXTable](
[appname] varchar(25),
[type] varchar(2),
[subtype] varchar(2),
[value] varchar(1)
) GO
CREATE TABLE [dbo].[datatable]
(
BICOL BigInt
)
insert into EXTABLE values('AA','F','F','1')
insert into EXTABLE values('KA','F','F','2')
insert into EXTABLE values('CA','F','F','3')
insert into DATATABLE values(1)
insert into DATATABLE values(2)
insert into DATATABLE values(3)
SELECT *
FROM datatable
WHERE BICOL NOT IN
(
SELECT CAST(value AS BIGINT)
FROM EXTABLE
WHERE appname = 'AA'
AND type = 'F'
AND subtype = 'F'
)
The results are the following :
BICOL
2
3
March 6, 2008 at 9:04 am
That's because all the data you inserted into the value field was able to be converted to BIGINT because it is numeric. That is why the error code is being generated by the query. He stated that there are some records with alpha characters in this field. The CAST fails as a result.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 6, 2008 at 9:06 am
First, NOT IN usually doesn't perform as well as an OUTER JOIN, so I'd change that right away. Second, you can check to see that values are a number by using 'ISNUMERIC(column) = 1 ' in the where clause. But it's likely to cause scans. The question is, why would you store valid numbers and strings in the same field?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2008 at 9:18 am
Grant Fritchey (3/6/2008)
First, NOT IN usually doesn't perform as well as an OUTER JOIN, so I'd change that right away. Second, you can check to see that values are a number by using 'ISNUMERIC(column) = 1 ' in the where clause. But it's likely to cause scans. The question is, why would you store valid numbers and strings in the same field?
Hi Grant,
We have one exclusion table that we use against many table in many different queries. We exclude against columns of various data types, so the value column in the exclusion table is set up as a varchar.
I'll be trying out the outer join suggestion that you and Timothy have given.
Thanks
JW
March 6, 2008 at 10:03 am
Thanks again Timothy and Grant,
The Left Outer Join works great.
Regards,
JW
March 6, 2008 at 10:24 am
Glad to hear it. IMHO you should always use set-based queries whenever possible. Avoid functions in the WHERE clause.
- Tim
Grant, nice to hear from you man... missed you on the call last night!
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 6, 2008 at 10:47 am
Yeah, sorry about that.
I sent an email to the group this morning. Did you get it?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply