Case Statement

[renee35]

Is there a way to create a case statement for the below formula that states if the number is < 0 then 0:

Right (replicate ('0',11) + cast (cast(a.ThisPdTaxableWages * 100 as int) as varchar(11)),11)

I have tried to create case statement using this code:

Case when Right (replicate ('0',11) + cast (cast(a.ThisPdTaxableWages * 100 as int) as varchar(11)),11) < 0 then 0
ELSE Right (replicate ('0',11) + cast (cast(a.ThisPdTaxableWages * 100 as int) as varchar(11)),11) END

but get this error:

Syntax error converting the varchar value '00000-48278' to a column of data type int.
Reason I am formatting it as such is because this is the requirement for sending it in a flat file.

Any help would be greatly appreciated!!

Thanks a bunch!!

-T

 

[M3Fan]

In your "Then" statement, try enclosing the 0 in single quotes, such as: then '0'. That way your column won't change data types based on the case statement.

 

[gmmastros]

When you use a case statement, it's important that each branch of the statement returns the same data type. If it doesn't, then SQL Server will use "Data Type Precedence Order" to auto-magically determine the data type of the entire case statement.

I think the way to solve your problem is to force the case statement to ALWAYS return a string.

[tt][blue]
Case When Right(...) > 0 Then [!]'[/!]0[!]'[/!]
Else Right(...)
End
[/blue][/tt]

By adding single-quotes around the 0, you are forcing it to be a string (instead of a number).

Case when Right (replicate ('0',11) + cast (cast(a.ThisPdTaxableWages * 100 as int) as varchar(11)),11) < 0 then [!]'[/!]0[!]'[/!]
                                        ELSE Right (replicate ('0',11) + cast (cast(a.ThisPdTaxableWages * 100 as int) as varchar(11)),11) END

Make sense?

-George

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

 

[renee35]

Tried enclosing the '0' in single quotes and still get the same error message.

Thanks!

Thanks a bunch!!

-T

 

[M3Fan]

Is there any reason that you need to cast the result of the equation into an INT and THEN cast it to varchar?

 

[renee35]

That's the only way I could get it to return the value I needed. I figured it out:

Case when cast(a.ThisPdTaxableWages * 100 as int) < 0 then replicate ('0',11)
ELSE Right (replicate ('0',11) + cast (cast(a.ThisPdTaxableWages * 100 as int) as varchar(11)),11) END

Thanks a bunch!

Thanks a bunch!!

-T