CASE Clause in SELECT Statement. Correct Formatting Question-CodePudding

I'm trying to display a custom (right term?) column (a column not present in the tables I'm drawing from) called myPercentage in this query I'm writing, but I think I'm getting the formatting of the CASE clause incorrect:

SELECT A.ID, A.lastDate, A.Numerator, A.Denominator
    ,(CASE
            WHEN (A.Numerator<>'0' AND A.Denominator='0') THEN '100%'
            WHEN (A.Numerator='0' AND A.Denominator<>'0') THEN '0%'
            WHEN (A.Numerator='0' AND A.Denominator='0') THEN '0%'
            ELSE (A.Numerator / A.Denominator)
    END) AS myPercentage
FROM Sample_Table_A AS A INNER JOIN Sample_Table_B AS B ON A.ID=B.ID
WHERE A.lastDate='1975-01-15'

The fields A.Numerator and B.Denominator are both of the value type money in the table.

I'm getting the follow error

Cannot convert a char value to money. The char value has incorrect syntax.

I'm guessing it's because I'm not formatting my query correctly. Whitespace, commas, semicolons, that type of stuff. Any advice would be greatly appreciated!

Thank you!

CodePudding user response：

You have to CAST the money column into a numeric number

CREATE TABLE Sample_Table_A(ID int,Numerator money,Denominator money ,lastDate date)
GO

CREATE TABLE Sample_Table_B(ID int)
GO

SELECT A.ID, A.lastDate, A.Numerator, A.Denominator
    ,(CASE
            WHEN (A.Numerator<>'0' AND A.Denominator='0') THEN '100%'
            WHEN (A.Numerator='0' AND A.Denominator<>'0') THEN '0%'
            WHEN (A.Numerator='0' AND A.Denominator='0') THEN '0%'
            ELSE (CAST(A.Numerator AS real)/ CAST(A.Denominator AS real))
    END) AS myPercentage
FROM Sample_Table_A AS A INNER JOIN Sample_Table_B AS B ON A.ID=B.ID
WHERE A.lastDate='1975-01-15'

GO

ID | lastDate | Numerator | Denominator | myPercentage
-: | :------- | --------: | ----------: | -----------:

db<>fiddle here

CodePudding user response：

Every branch of a CASE expression must be able to return a value in a data type compatible with (and of the highest data type precedence across) all branches. It's not possible to convert a string like 100% to a type compatible with a numeric expression like 5/10. Your query should probably be something like this:

SELECT A.ID, 
    CASE WHEN A.Denominator = 0 THEN
      CASE WHEN A.Numerator = 0 THEN 0 ELSE 100 END
         WHEN A.Numerator   = 0 THEN 0 
         ELSE (100 * A.Numerator / A.Denominator) END AS MyPercentage
         FROM dbo.Sample_Table_A AS A
         INNER JOIN dbo.Sample_Table_B AS B ON A.ID = B.ID
         WHERE A.lastDate = '19750115';

Then if you want to put % characters on it, format it to n decimal places, etc., do that at the presentation layer. If you have to do it in T-SQL, just do it after the CASE expression is processed:

;WITH MakePretty AS
(
  SELECT A.ID, 
    CASE WHEN A.Denominator = 0 THEN
      CASE WHEN A.Numerator = 0 THEN 0 ELSE 100 END
         WHEN A.Numerator   = 0 THEN 0 
         ELSE (100 * A.Numerator / A.Denominator) END AS MyPercentage
         FROM dbo.Sample_Table_A AS A
         INNER JOIN dbo.Sample_Table_B AS B ON A.ID = B.ID
         WHERE A.lastDate = '19750115'
)
SELECT ID, MyPercentage = CONCAT(CONVERT(decimal(5,2), MyPercentage),'%')
  FROM MakePretty;

Example db<>fiddle