October 28, 2009 at 12:05 pm
I have a view: The view does and should only bring back year column as an integer.
The problem is when I query this view and treat the year as an integer I get the message:
Conversion failed when converting the varchar value '<All Applicable Years>' to data type int.
Any ideas? (there is data in the table <all applicable years>) but the view doesn't bring that back
SELECT dbo.Category.Description AS Engine, convert(int,category_1.Description) as Year, Category_2.Description AS Model, Category_3.Description AS Make,
dbo.Category.GenCategoryID, dbo.Category.Polk, dbo.Category.BookMark, IsNumeric(Category_1.Description) AS Num, dbo.Category.MaintID
FROM dbo.Category AS Category_3 INNER JOIN
dbo.Category AS Category_2 INNER JOIN
dbo.Category INNER JOIN
dbo.Category AS Category_1 ON dbo.Category.ParentID = Category_1.GenCategoryID ON Category_2.GenCategoryID = Category_1.ParentID ON
Category_3.GenCategoryID = Category_2.ParentID
WHERE len(category_1.description) < 5 and (IsNumeric(Category_1.Description) = 1) AND (dbo.Category.CategoryTypeID = 4) and Category_1.CategoryTypeID = 3
select Make, Model, year, Engine, Polk, Bookmark, GenCategoryID from vwEquipment
where IsNumeric(year) = 1 and year >= 2000
October 28, 2009 at 12:07 pm
I would guess some row contains character value for column "category_1.Description" that's making the conversion to fail.
October 28, 2009 at 12:15 pm
I am sorry I need to be more clear.
I know that we have data in the description that is not numeric.
The view is suppose to act as a (sub table) or so I thought.
That isn't the case. When I run the query:
select Make, Model, year, Engine, Polk, Bookmark, GenCategoryID from vwEquipment
where year like '<All%'
I get no data and this is because the view is doing only half it's job.
What do I need to do to make the view act as a real partial table.
If I insert all the data from the view into a different table i.e vwEquipmentTable the query:
select Make, Model, year, Engine, Polk, Bookmark, GenCategoryID from vwEquipment
where year > 2000
would work.
October 28, 2009 at 1:05 pm
I figured out a way around it.
October 28, 2009 at 1:12 pm
foxjazz-962651 (10/28/2009)
I figured out a way around it.
Okay, since you did ask for help, care to enlighten us with your solution? Others may benefit.
October 29, 2009 at 8:48 am
Lynn Pettis (10/28/2009)
foxjazz-962651 (10/28/2009)
I figured out a way around it.Okay, since you did ask for help, care to enlighten us with your solution? Others may benefit.
Sorry Lynn, time wasn't on my side due to snow.
What I had done in my previous database was to add a YearID as an integer field. And populate that with integer data. And modified the view to use that as year.
Basically views don't really act as subtables in sql server. It is unfortunate, but a fact.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply