Converting decimal values to strings in SQL Server 2012 view

Data and Databases
1

I’m working on moving a formula from Crystal Reports to a SQL Server view. The original formula uses decimal values for different conditions. Here’s what I’m trying to do:

CASE 
  WHEN InstitutionIdentifier = 'TPOCFBOI' THEN 1.10
  WHEN InstitutionIdentifier = 'TPOBCB' THEN 0.50
  WHEN InstitutionIdentifier = 'TPOCFB' THEN 0.75
  WHEN Region = 'WMG' THEN 0.01
  WHEN LoanOfficerName = 'Margaret S. Smith' THEN 0.875
  ELSE 0.00
END AS DecimalOutput

How can I make sure this works in a SQL Server view and returns a decimal with 3 decimal places? I tried using DECLARE and SELECT INTO, but it didn’t work. Any ideas on how to fix this?

2

For your SQL Server view, you don’t need to use DECLARE or SELECT INTO. The CASE statement you’ve provided should work fine, but to ensure you always get 3 decimal places, you can use the CAST or CONVERT function. Here’s a modified version of your query:

CASE 
  WHEN InstitutionIdentifier = 'TPOCFBOI' THEN CAST(1.10 AS DECIMAL(5,3))
  WHEN InstitutionIdentifier = 'TPOBCB' THEN CAST(0.50 AS DECIMAL(5,3))
  WHEN InstitutionIdentifier = 'TPOCFB' THEN CAST(0.75 AS DECIMAL(5,3))
  WHEN Region = 'WMG' THEN CAST(0.01 AS DECIMAL(5,3))
  WHEN LoanOfficerName = 'Margaret S. Smith' THEN CAST(0.875 AS DECIMAL(5,3))
  ELSE CAST(0.00 AS DECIMAL(5,3))
END AS DecimalOutput

This approach ensures consistent decimal precision across all conditions in your view. The DECIMAL(5,3) specifies a total of 5 digits with 3 after the decimal point, which should cover your needs based on the values provided.

3

yo finn, have u checked out the CAST function? it’s pretty neat for this kinda stuff. try something like:\n\nCAST(CASE \n WHEN InstitutionIdentifier = ‘TPOCFBOI’ THEN 1.10\n – other conditions\n ELSE 0.00\nEND AS DECIMAL(5,3)) AS DecimalOutput\n\nthis shud give u exactly 3 decimal places in ur view. lemme know if it helps!

4

hey Finn, have you tried using the ROUND function? it might help with your decimal issue. something like:

ROUND(CASE
WHEN InstitutionIdentifier = ‘TPOCFBOI’ THEN 1.10
WHEN InstitutionIdentifier = ‘TPOBCB’ THEN 0.50
– other conditions
ELSE 0.00
END, 3) AS DecimalOutput

what do you think? cud this work for your view?