CODEWITHSUNDEEP
db2
FVod
FVod

Reputation: 2295

Select statement to return one value or another one if the first one is null

I have a Book's table as follows:

id     locale     name         book_id     ...
1      "en-GB"    The book     421
2      "fr-FR"    Le livre     421
...

I need to SELECT the book's name for a given locale (for instance 'fr-FR') but then, if the first one is not set, then retrieve the name with the locale by default ('en-GB') which will be always set. How could I do that? I'm using a DB2. I've tried the following statement:

SELECT CASE WHEN B.NAME IS NOT NULL THEN B.NAME ELSE A.NAME END AS NAME FROM BOOK A JOIN BOOK B ON B.BOOK_ID = A.BOOK_ID WHERE A.LOCALE =  "en-GB" AND B.LOCALE = "fr-FR"

However, this is only working when both locales are defined, but not in case that I need (when the second locale is not set).

Thanks in advance.

EDIT WITH MY OWN ANSWER: I have finally used an UNION to achieve that:

SELECT NAME FROM BOOK WHERE BOOK_ID = 421 AND LOCALE = "en-GB"
UNION ALL
SELECT NAME FROM BOOK WHERE BOOK_ID = 421 AND LOCALE = "fr-FR"
FETCH  FIRST 1 ROW ONLY

Upvotes: 1

Views: 123

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Try this:

select *
from mytable
where book_id=421 and locale in 
--('*', 'en-GB')
('fr-FR', 'en-GB')
order by case when locale='en-GB' then 1 else 0 end
fetch first 1 row only;

Upvotes: 1

Fabian Claasen
Fabian Claasen

Reputation: 284

I said View in my previous answer I meant Stored Procedure. You might want to take a look to creating a Stored Procedure. In a SP you can add if else statements.

CREATE PROCEDURE SomeProcedure
AS
BEGIN
    IF Exists(SELECT 1 FROM Book WHERE locale = "en-GB")
        SELECT * FROM Book WHERE locale = "fr-FR"
    ELSE
        SELECT * FROM Book WHERE locale = "en-GB"
END

Something like this.

Upvotes: 0

Related Questions