Home > Back-end >  SELECT a decimal if NULL show N/A else show USD Amount
SELECT a decimal if NULL show N/A else show USD Amount

Time:02-27

I have a field called Amount type of Decimal(12,2).

Amount, can accept NULL and decimal value example 100.00.

SELECT
NULLIF(Amount, 'N/A') AS Amount
FROM PRODUCTS;

This cause:

Error converting data type varchar to numeric.

I want to SELECT a decimal
if NULL show 'N/A'
else show 'USD Amount'

Hope you can help me. Thanks.

CodePudding user response:

Convert Amount to a varchar then apply isnull

SELECT ISNULL(CONVERT(VARCHAR(30),Amount),'N/A') as Amount FROM PRODUCTS;

CodePudding user response:

Here the answer:

SELECT IIF(Amount IS NULL, 'N/A', 'USD'   CONVERT(VARCHAR(15),Amount)) as Amount FROM PRODUCTS;

Reference link : SQL Function IIF

Thanks.

  • Related