decimal places of a variable numeric field depending on the CASE in SQL

Question

decimal places of a variable numeric field depending on the CASE in SQL

Navigation

#1 by (1 votes)

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, is it 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 I should return a result with 2 decimal places, and if @CODJUROS is 2 I should return a result with 5 decimal places, but both return me with 5 decimal places.

sql sql-server

asked by anonymous 21.09.2017 / 22:33

1 answer

How do I resolve struct declaration warning? View Model receiving Null values

score 1 · Answer 1

Good morning.

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

Here's the 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]