including decimal point value

Hi federicomarinuci,

Thanks for replying to my post.

I could modify the stored procedure to the following:

ALTER PROCEDURE [dbo].[spGetCustomerServiceMetrics]

AS
   SELECT metric_id,  description, metric =
      CASE metric_id
      WHEN 1 THEN CAST(metric AS VARCHAR(2)) + '%'
      WHEN 2 THEN CAST(metric AS VARCHAR(10)) + ' seconds'
                      WHEN 3 THEN CAST(metric AS VARCHAR(2)) + '.0'
      ELSE CAST(metric AS VARCHAR(10))
      END
FROM dbo.customer_service_metric

But, the problem with that is the record will change in the next quarter to possibly 8.2 or someother decimal value, then it would display as  8.2.0 and then would not be good.

Is there a better way to correct this?

SELECT     metric_id,  description, CASE (metric%1.0)
WHEN 0.0 THEN CAST(metric AS VARCHAR(10)) + '.0'
ELSE CAST(metric AS VARCHAR(10))
END AS metric

This way the numbers which are integer gets displayed with the ".0" suffix.

>WHEN 1 THEN CAST(metric AS VARCHAR(2)) + '%'
its the issue with the 'real' datatype , another option is to convert it to decimal

SELECT metric_id,  description, metric =
      CASE metric_id
      WHEN 1 THEN  cast( cast(Metric as decimal(10,1)) as varchar(10) )  + '%'
      WHEN 2 THEN CAST(metric AS VARCHAR(10)) + ' seconds'
                      WHEN 3 THEN CAST(metric AS VARCHAR(2)) + '.0'
      ELSE CAST(metric AS VARCHAR(10))
      END
FROM dbo.customer_service_metric

Hi federicomarinucci,

I received the following error:

The data types real and numeric are incompatible in the modulo operator.

From looking at the select and case statement above, I think I see another issue that will occur.

For record 2, I have to append the text ' seconds' because the value that is stored in the table is 25 and it needs to be displayed as '25 seconds'.
For record 1, I append the text '%' because the value that is stored in the table is 91 and it needs to be displayed as '91%'

Does that make sense?  Basically, I'm displaying customer service metrics...i.e.:

91%
25 seconds
8.0
4.7
4.6

so, the values in the table are just:
91
25
8
4.7
4.6

Thanks for your help with this.
Regards.

So, we encapsulate the current CASE statement in another one, assuming metric_id define if the metric value is a percentage, seconds or a real value.

SELECT metric_id,  description,
CASE (metric_id)
WHEN 1 THEN CAST(metric AS VARCHAR(10) +'%'
WHEN 2 THEN CAST(metric AS VARCHAR(10) +' seconds'
WHEN 3 THEN
CASE (metric%1.0)
WHEN 0.0 THEN CAST(metric AS VARCHAR(10)) + '.0'
ELSE CAST(metric AS VARCHAR(10))
END
ELSE CAST(metric AS VARCHAR(10))
END AS metric

That does make perfect sense to me...I understand exactly what you are explaining....thank you!

The only problem I'm having is actually executing the code to alter the stored procedure.  I'm using SQL Server 2005.

I get the error: The data types real and numeric are incompatible in the modulo operator.

I did a search on google for this error and it did not bring up any hits on the error.

I think the error is caused on this line: CASE (metric%1.0)

Any idea on that error?

If the metric field is a real, you have to cast 1.0 to real:

CASE (metric%CAST(1.0 AS real))

This should work!

Hmmm, well, I'm getting an incorrect syntax error near CAST

First, I tried this:

ALTER PROCEDURE [dbo].[spGetCustomerServiceMetrics]

AS
   SELECT metric_id,  description, metric =
      CASE metric_id
      WHEN 1 THEN CAST(metric AS VARCHAR(2)) + '%'
      WHEN 2 THEN CAST(metric AS VARCHAR(10)) + ' seconds'
      WHEN 3 THEN
            CASE (metric CAST(1.0 AS REAL))
            WHEN 0.0 THEN CAST(metric AS VARCHAR(10)) + '.0'
            ELSE CAST(metric AS VARCHAR(10))
            END
      ELSE CAST(metric AS VARCHAR(10))
      END
FROM dbo.customer_service_metric


As a quick workaround, I modified to this:

ALTER PROCEDURE [dbo].[spGetCustomerServiceMetrics]

AS
   SELECT metric_id,  description, metric =
      CASE metric_id
      WHEN 1 THEN CAST(metric AS VARCHAR(2)) + '%'
      WHEN 2 THEN CAST(metric AS VARCHAR(10)) + ' seconds'
      WHEN 3 THEN
            CASE (metric)
            WHEN 8.0 THEN CAST(metric AS VARCHAR(10)) + '.0'
            ELSE CAST(metric AS VARCHAR(10))
            END
      ELSE CAST(metric AS VARCHAR(10))
      END
FROM dbo.customer_service_metric

The second attempt worked, but of course, the case statement is looking for 8.0 and if it changes to anything else, it breaks.

It seems it doesn't like the cast in the case line.

Thanks for all your help!  That solved the problem.  I appreciate your help.

Best Regards!

Excellent solution!

Thank you!