-1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
henryloke
  • 67
  • 5
  • 4
    In database speak its not a field, its a column. – Dale K Feb 25 '22 at 09:58
  • 4
    This is something for your presentation layer *not* the SQL layer. Also `NULLIF(Amount, 'N/A')` makes no sense here; a `decimal` can *never* have the value `'N/A'` you would want `ISNULL` if you had to do this. `NULLIF` returns `NULL` *if*` the first parameter is equal to the second, not returns the second parameter if the first is `NULL`. – Thom A Feb 25 '22 at 09:58
  • 3
    `NULLIF` requires both arguments to be the same datatype, so convert you number to a varchar if you want to `nullif` it with "N/A". – Dale K Feb 25 '22 at 09:59
  • 1
    Note that replacing `NULLIF` with `ISNULL` will generate the same error; `ISNULL` will implicitly cast the second parameter to the datatype of the first (and, again, `'N/A'` is is not a valid `decimal` value). This is why I explicitly stated you should be doing this in your presentation layer (as casting numerical data to a string based data type is generally a very poor idea). – Thom A Feb 25 '22 at 10:06
  • It for listing (display data) use only. – henryloke Feb 25 '22 at 12:55

2 Answers2

0

Convert Amount to a varchar then apply isnull

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

Kavita GK
  • 11
  • 3
0

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.

henryloke
  • 67
  • 5