CODEWITHSUNDEEP
c#sql
maephisto
maephisto

Reputation: 333

?? operator in sql


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!

Upvotes: 3

Views: 204

Answers (6)

onedaywhen
onedaywhen

Reputation: 57023

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'.

Upvotes: 6

codingbadger
codingbadger

Reputation: 44004

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

Upvotes: 0

Theofanis Pantelides
Theofanis Pantelides

Reputation: 4854

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

Upvotes: 1

thomaux
thomaux

Reputation: 19728

To tackle such problems, you can use the CASE statemnt

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

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798814

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

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063068

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).

Upvotes: 8

Related Questions