CODEWITHSUNDEEP
sqloracle-database
vw_tsc
vw_tsc

Reputation: 7

Using the division operation in select statement

Someone wrote the following select statement:

select ... ,   FLOOR (a / b) AS c, ...

You can tell a problem of the select statement. If b is zero, the query will lead to an exception.

How to place a condition here so that if b is zero, the c will yeild zero as well?

Update:

A case statement can be used to sovle this problem.

Upvotes: 0

Views: 274

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use nullif():

select ... , FLOOR(a / NULLIF(b, 0)) AS c, ...

I much prefer having NULL as in the unknown value. If you want 0, you can use:

select ... , COALESCE(FLOOR(a / NULLIF(b, 0)), 0) AS c, ...

Of course, this assumes that neither a nor b are NULL themselves.

Upvotes: 1

rgettman
rgettman

Reputation: 178243

Use a case expression, where you can control the value and avoid the exception.

select ..., CASE WHEN b IS 0 THEN 0 ELSE (a / b) END AS c, ...

Upvotes: 4

Related Questions