decimals of a variable Numeric field depending on CASE in SQL

Asked

Viewed 1,371 times

2

I have a query where, depending on a parameter, I need the result in a field to be 5 decimal places or 2 decimal places, this is possible in SQLSERVER 2008?

Example

DECLARE @VALOR DECIMAL(18,2), @PORCENTOJUROS DECIMAL(18,2), @CODJUROS INT 

SET @VALOR = 5000.00
SET @PORCENTOJUROS = 2.0
SET @CODJUROS = 1

SELECT
CASE 
WHEN @CODJUROS = 1  THEN CAST((((@VALOR / 30.0) / 100.0) * @PORCENTOJUROS) AS NUMERIC(15,2)) 
WHEN @CODJUROS = 2  THEN  CAST((@PORCENTOJUROS / 30.0) AS NUMERIC(12,5)) 

ELSE 0.0 END [VALOR_MORA]

In this example, if @CODJUROS is 1 should return me a result with 2 decimal places, and if @CODJUROS is 2 should return me a result with 5 decimal places, however, the two return me with 5 decimal places.

sql sql-server

  • It seems to me a case of Problema XY. Can you explain why you want to reach this solution?

  • This query mounts a customizable layout for my clients through pivot, this value field mora is written in a text file, I complete with "0" left to always be with 17 characters so the decimals influence the final result.

1 answer

1

Good morning.

A friend here at work arrived at a "solution", converting the field to varchar(MAX)

Follow answer to help others:

DECLARE @VALOR DECIMAL(18,2), @PORCENTOJUROS DECIMAL(18,2), @CODJUROS INT 

SET @VALOR = 5000.00
SET @PORCENTOJUROS = 2.0
SET @CODJUROS = 1

SELECT
CASE 
WHEN @CODJUROS = 1  THEN cast(CAST((((@VALOR / 30.0) / 100.0) * @PORCENTOJUROS) AS decimal(15,2)) AS VARCHAR(max))
WHEN @CODJUROS = 2  THEN cast( CAST((@PORCENTOJUROS / 30.0) AS decimal(12,5)) AS VARCHAR(max))

ELSE cast(0.0  AS VARCHAR(max)) END [VALOR_MORA]

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.