?? operator in sql

Question

Hi!
I have the following issue :
We have this query :

select price*hours as payment from table employees

Now, if the result of that multiplication it's 0, I'd like payment to be "x", not 0.
Translated in nonsql this would mean :

(price*hours) != 0 ? (price*hours) : "x"

Any ideas how could i implement this sql command ?
Thanks!

Answer 1

Well, ?? would apply to NULL - in which case COALESCE or ISNULL - but you seem to mean 0 - in which case just:

SELECT ...blah...,
       CASE price*hours WHEN 0 THEN 'x' ELSE price*hours END AS [payment]
...more blah...

However, I would advise doing this as a UI concern rather than in the database. You may also want to think about how close to 0 it needs to be (floating point rounding etc).

Answer 2

CASE
  WHEN somevar = 0 then "x"
  ELSE somevar
  END

Answer 3

To tackle such problems, you can use the CASE statemnt

SELECT  payment = CASE
        WHEN price * hours = 0 THEN 'X'
        ELSE price * hours
        END

Answer 4

select payment =
  case price*hours
    when 0 THEN 'x'
    else price*hours
  end
from table employees

Answer 5

Well you would have to convert the resulting number in to a string or it will fail as "x" isnt a number.

Maybe something like this:

Select Case 
       When Price * Hours = 0 Then "x" 
       Else Convert(varchar(50), (Price * Hours)) End as "Price"

From dbo.Table

Answer 6

SELECT COALESCE(CAST(NULLIF(price * hours, 0) AS VARCHAR), 'x') AS payment_text
  FROM employees;

...but I agree with @Marc Gravell that this kind of formatting should be done in the 'front end'.