CODEWITHSUNDEEP
sqlpostgresqlconditional-statements
vdegenne
vdegenne

Reputation: 13288

add value1 to value2 OR 0 (zero) to value2 if value1 IS NULL

This is a example

SELECT
(time + (SELECT vector FROM vectors WHERE type=1)) AS modified_time
FROM times;

I want to add vector to time from the vectors table where the vector's type is 1. Sometimes there is one vector with type set to 1 (always only one, that is why I didn't use LIMIT in the query), but sometimes there is no one vector with type set to 1.

In the second case, the sub-query in the query above will return null and so time + null will be null.

So I need to make a fallback in case the sub-query returns null, something like :

... (time + ((SELECT vector FROM vectors WHERE type=1) OR 0)) AS modified_time

How can I make that ?

Upvotes: 0

Views: 117

Answers (3)

Delvy Ortega
Delvy Ortega

Reputation: 11

I think that you want to do this:

SELECT
(time + ISNULL(SELECT vector FROM vectors WHERE type=1),0 OR Other Subquery) AS modified_time
FROM times;

So If there are more than one Aplly SUM to vector: 

SELECT
(time + ISNULL(SELECT SUM(vector) FROM vectors WHERE type=1),0 OR Other Subquery) AS modified_time
FROM times;

Upvotes: 1

Esteban P.
Esteban P.

Reputation: 2809

Try to use COALESCE 

SELECT
(time + COALESCE((SELECT vector FROM vectors WHERE type=1),0)) AS modified_time
FROM times;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

If you know there is only one row, let's guarantee it using max() . . . then you can use coalesce():

SELECT (time + (SELECT COALESCE(MAX(vector), 0) FROM vectors WHERE type = 1)) AS modified_time
FROM times;

There are other approaches, but I find this to be the simplest if you leave the subquery in the SELECT clause.

Upvotes: 1

Related Questions