January 22, 2016 at 9:38 am
I've never seen this error before. I'm not sure I understand what's going on here. We have a bunch of tables in our database with accounting periods on the end of them, so <table name>_201212, <table name>_201301, <table name>_201302, etc....
I have a view that selects from information_schema.tables and does a substring on these tables pulling off that period, so I can get a list of periods we have tables for. Once I have the period in the view I cast it as an int. When I look in the object explorer it shows this column as an int.
When I run this query it returns a proper result:
select distinct
Period
from <table name>
where Period = 201312
order by 1
When I run this query
select distinct
Period
from <table name>
where Period >= 201312
order by 1
I get an error saying:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'MSalreadyhavegeneration' to data type int.
Hoping to get a little background here on what may be going on.
January 22, 2016 at 9:56 am
A view is nothing else than a saved query. It has no compilation by itself and it's not aware of the values stored in the underlying tables.
That said, somewhere in your code, you're probably missing a validation to prevent non integer values be converted to int.
My guess is that you're using something like CONVERT( int, RIGHT( name, LEN(name) CHARINDEX('_', name))) and extracting it from sys.all_objects. That will return objects that you don't intend to use.
Try using this WHERE clause in your view definition.
WHERE name like '%[_][0-9][0-9][0-9][0-9][0-9][0-9]'
January 22, 2016 at 10:04 am
Thanks for the reply.
Here's the column I'm selecting in the view:
cast(substring(table_name, charindex('_', table_name) + 1, 100) as int) as Period
I'm casting this as an int and it runs fine when I do a select *, as well as filtering with an = <integer value>. It only throws that error when I use >= or <=.
The other weird thing is if you dump it into a temp table (select * into #temp from view) I can then filter that temp table using >= or <=.
January 22, 2016 at 10:58 am
if you added a table named Customer_Invoice, you would get the error
Conversion failed when converting the nvarchar value 'Invoice' to data type int.
Luis hit the nail on the head: you are grabbing tables that are outside of your formatting assumption.
you want to exclude them with a WHERE clause.
Lowell
January 22, 2016 at 11:15 am
Lowell (1/22/2016)
if you added a table named Customer_Invoice, you would get the errorConversion failed when converting the nvarchar value 'Invoice' to data type int.
Luis hit the nail on the head: you are grabbing tables that are outside of your formatting assumption.
you want to exclude them with a WHERE clause.
Thanks for the reply. This is a weird one. I was filtering the tables based on the table_schema column. I was only including tables that had the period on the end.
I added this to the where clause:
isnumeric(substring(table_name, charindex('_', table_name) + 1, 100)) = 1
I still get the exact same result set back when I run the query defining the view, but I now am able to filter based on my period column.
January 22, 2016 at 11:47 am
Your problem stems from the fact that the SQL Server Query Optimizer can rearrange conditions as it sees fit. With the equality constraint it apparently picks an execution plan where it only attempts to convert to int the values that are actually numeric. With the inequality, it picks a different plan and now it tries to convert texts to int - and fails.
Can you try using where Period >= '201312' (by embedding the constant in quotes, it becomes a string and now no conversion is needed).
Do test, for the > semantics are different for string then they are for integers. If the period substring is always six digits for the rows that qualify the other conditions, you'll be fine, but otherwise my suggestion can return incorrect results. I may have an alternative solution if that is the case, but please try this first.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply