Error converting data type

Question

Error converting data type

amar_kaur16

SSC Eights!

Points: 984
More actions
October 9, 2013 at 10:27 am

#284427

I have a query that is comparing two sections and the I am converting the type to numeric (38,0) to compare them.
All the section should be numeric ex: ‘1123’ or ‘1907’ but one of the section has alphanumeric characters for the previous year(2012 and 2011) and I am running query to select the data for the current year(2013)
So I had filtered out previous year data by using where clause and the query was working fine earlier but today I am getting the error converting data type varchar to numeric even though I am selecting only for current year which does not contain alphanumeric sections.
Select * from professortbl A left join classtable B where
Cast(A.class_section as numeric(38,0))= Cast (B.class_Section as Numeric(38,0))
Where term >=’2013’
I am not sure why something would stop working suddenly if it has worked earlier.
PLease help.
Thnaks,
Blyzzard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

It could change because the Query Optimizer changed the execution plan and is applying the CAST within the JOIN before the WHERE clause.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

amar_kaur16 SSC Eights! Points: 984 More actions · Answer 2

Thanks for your response.

It could be possible as the updates were being run a day before.

Is it possible to go back to the original execution plan?

Thanks,

Blyzzard

Sean Lange SSC Guru Points: 286589 More actions · Answer 3

amar_kaur16 (10/9/2013)
Thanks for your response.
It could be possible as the updates were being run a day before.
Is it possible to go back to the original execution plan?
Thanks,
Blyzzard

Execution plans are cached. You can't go back and retrieve one that doesn't exist anymore.

Given your query:

Select * from professortbl A left join classtable B where

Cast(A.class_section as numeric(38,0))= Cast (B.class_Section as Numeric(38,0))

Where term >=’2013’

Why do you even need to cast in your where clause? The only reason I can think of is if you are storing numbers in a character column and you have left padded those values with 0's. Otherwise you should just be able to remove the cast entirely and get the same results.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amar_kaur16 SSC Eights! Points: 984 More actions · Answer 4

In the both source tables, class section is varchar but I was using cast because of the alphanumeric characters for the previous year.

but I think you are right as I am only selecting current year where class section is not alphanumeric, it should work.

Thanks,

Blyzzard