MS SQL Server New Users - Simplifying an SQL CASE statement

Author

Simplifying an SQL CASE statement

James_101

2005-11-25, 11:23 am

In a SELECT command used in SQL Query Analyzer, I have written the following
code. With three functions in sequence (CAST, CAST, Round), the code seems
unnecessarily complex:

CASE
WHEN MyVar1='01' THEN CAST(CAST(Round((MyV
ar2)/22.0,0) AS int) AS char(5))
ELSE '-'
END AS Core_Tools

MyVar1 and MyVar2 are fields in the same table. Both fields receive integer
data from another application. MyVar1 has the char data type and MyVar2 is
tinyint.

The intent: If MyVar1=01 then MyVar2/22 is rounded to the nearest integer
and displayed. If MyVar1<>01, then a hyphen is displayed.

If MyVar2 is divided by 22, then the result is truncated, not rounded. To
get the statement to round, I divide by 22.0. Then to get rid of the zeros
to the right of the decimal point, I convert to int data. Because the Case
function apparently requires that both results use the same data type and
since the hyphen is a string, I convert the rounded number to char.

This code is used 5 times in the query with different variables and I would
like to simplify it. The only option I see is to split the CASE statement
into two CASE statements, each without the ELSE. Then, the first CASE
statement can display the int data type and the second can display a
character.

Is there another option?

Thanks.

Jim

Hugo Kornelis

2005-11-25, 8:23 pm

On Fri, 25 Nov 2005 07:49:04 -0800, James_101 wrote:

>In a SELECT command used in SQL Query Analyzer, I have written the following
>code. With three functions in sequence (CAST, CAST, Round), the code seems
>unnecessarily complex:
>
>CASE
>WHEN MyVar1='01' THEN CAST(CAST(Round((MyV
ar2)/22.0,0) AS int) AS char(5))
>ELSE '-'
>END AS Core_Tools
>
(snip)
>
>Is there another option?

Hi Jim,

How about :

CASE WHEN MyVar1 = '01'
THEN STR(MyVar2/22.0, 5, 0)
ELSE '-'
END AS Core_Tools

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

James_101

2005-11-25, 8:23 pm

CASE WHEN MyVar1 = '01'
THEN STR(MyVar2/22.0, 5, 0)
ELSE '-'
END AS Core_Tools

Thanks Hugo. I understand that the argument for STR should be a floating
point expression (which I don't fully understand). Can I still use the
tinyint data type for MyVar2 in the table? I like tinyint because it uses
only 1 byte.

Jim

Hugo Kornelis

2005-11-25, 8:23 pm

On Fri, 25 Nov 2005 14:48:02 -0800, James_101 wrote:

>CASE WHEN MyVar1 = '01'
> THEN STR(MyVar2/22.0, 5, 0)
> ELSE '-'
>END AS Core_Tools
>
>Thanks Hugo. I understand that the argument for STR should be a floating
>point expression (which I don't fully understand). Can I still use the
>tinyint data type for MyVar2 in the table? I like tinyint because it uses
>only 1 byte.
>
>Jim

Hi Jim,

Did you test it? Yes, MyVar2 can remain tinyint.

Working inside out, MyVar2 (tinyint) / 22.0 (numeric(3,1)) results in a
numeric(10,6)l. This is implicitly converted to float, becuase that's
what STR expects. And STR will do the rounding for you.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links