Case Error converting data type varchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case Error converting data type varchar to numeric

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-25 : 13:03:25

Hi,

I have an error
Error converting data type varchar to numeric.

te.strNote can be alpha and numeric
When is Alpha i need to show strNote normally
When numeric i need to convert it!

Here what i have any idea tks?
Tks

SELECT     tev.strMatiere,
		   (SELECT stdlastn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strLastName,
		   (SELECT stdfirstn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strFirstName,
		   tee.intEvaluationElementId,tev.strTitre as strTitreP, tee.strTitre as strTitreS,
		   strNote = CASE
					 WHEN isnumeric(te.strNote) = 1 then cast(te.strNote as Decimal(10,2)) / tee.intPonderation * 100
					 ELSE UPPER(te.strNote)
					 ENDfrom       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
INNER JOIN tblM2Evaluation as tev 
on         tev.intEvaluationId = tee.intEvaluationId
WHERE      te.intElevesId = xxxxxxx
and        te.strNote <> ''
ORDER BY   tev.strMatiere,te.intEvaluationElementId,strLastName,strFirstName

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-25 : 13:07:40

try this,

SELECT     tev.strMatiere,
		   (SELECT stdlastn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strLastName,
		   (SELECT stdfirstn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strFirstName,
		   tee.intEvaluationElementId,tev.strTitre as strTitreP, tee.strTitre as strTitreS,
		   strNote = CASE
					 WHEN isnumeric(te.strNote) = 1 then convert(varchar,cast(te.strNote as Decimal(10,2)) / tee.intPonderation * 100)
					 ELSE UPPER(te.strNote)
					 END
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
INNER JOIN tblM2Evaluation as tev 
on         tev.intEvaluationId = tee.intEvaluationId
WHERE      te.intElevesId = xxxxxxx
and        te.strNote <> ''
ORDER BY   tev.strMatiere,te.intEvaluationElementId,strLastName,strFirstName

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-25 : 13:11:43

Hi tks it work?

Is that the way to convert convert(varchar,cast(te.strNote as Decimal(10,2))??

I not sure what i was doing wrong?

Tks

quote:

Originally posted by sakets_2000

try this,

SELECT     tev.strMatiere,
		   (SELECT stdlastn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strLastName,
		   (SELECT stdfirstn FROM Bulletin.dbo.tblEleves WHERE stdnumber = te.intElevesId) as strFirstName,
		   tee.intEvaluationElementId,tev.strTitre as strTitreP, tee.strTitre as strTitreS,
		   strNote = CASE
					 WHEN isnumeric(te.strNote) = 1 then convert(varchar,cast(te.strNote as Decimal(10,2)) / tee.intPonderation * 100)
					 ELSE UPPER(te.strNote)
					 END
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
INNER JOIN tblM2Evaluation as tev 
on         tev.intEvaluationId = tee.intEvaluationId
WHERE      te.intElevesId = xxxxxxx
and        te.strNote <> ''
ORDER BY   tev.strMatiere,te.intEvaluationElementId,strLastName,strFirstName

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-25 : 13:16:11

Sorry, I didn't understand you. Did that work ?

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-25 : 13:20:13

Hehe sorry!

Yes it work perfectly!

I was just saying i don't understant why my query did't not work and yours did!

quote:
Originally posted by sakets_2000

Sorry, I didn't understand you. Did that work ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-25 : 13:29:38

strNote column is numeric for some rows and varchar for some,depending on which case statement it qualifies for.
You'd have to convert your numeric to varchar because of this to maintain consistency in datatype. Else, sql server would try and convert itself. Had all strNote values been varchar or all values been numeric, it wouldn't have given you the same error.

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-25 : 13:31:59

Tks for the explanation
!

quote:
Originally posted by sakets_2000

strNote column is numeric for some rows and varchar for some,depending on which case statement it qualifies for.
You'd have to convert your numeric to varchar because of this to maintain consistency in datatype. Else, sql server would try and convert itself. Had all strNote values been varchar or all values been numeric, it wouldn't have given you the same error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources