Syntax Error Converting VarChar

[luvtitans50]

Syntax error converting the varchar value 'N/A' to a column of data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

I am getting the above error when running the below statement:

Case when sum(newpositions) = 0 then 'N/A'
when sum(numerator) = 0 then '100%'
ELSE
sum(numerator)/NULLIF(sum(newpositions),0) END as timetofillpctg

Please advise.

Thanks,

 

[gmmastros]

This is obviously a case of "Case When Data Type Problems". I blogged on this a while ago.

http://blogs.lessthandot.com/index....erver/sql-server-case-when-data-type-problems

Case when sum(newpositions) = 0 then 'N/A'
when sum(numerator) = 0 then '100%'
ELSE
[!]Convert(VarChar(20), [/!]sum(numerator)/NULLIF(sum(newpositions),0)[!])[/!] END as timetofillpctg

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[luvtitans50]

Thanks! Now I need for the results to come out as a decimal (I am trying to get the percentage). I am trying to wrap the actual calculation part:

Convert(VarChar(20), sum(numerator)/NULLIF(sum(newpositions),0))* 100 to get the percentage and I am getting the same error:

Syntax error converting the varchar value 'N/A' to a column of data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

In the end I need that Case statement to return a decimal (percentage).

Thanks a million!!

 

[gmmastros]

In the end I need that Case statement to return a decimal (percentage).

You can't. Did you read the blog I pointed you to?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

The CASE statement can not return the decimal if you want to use N/A or other character variables in your expression.

You need to cast to character the percentage as well, e.g.

Convert(VarChar(20), sum(numerator)/NULLIF(sum(newpositions),0)* 100.00)

PluralSight Learning Library