Ilya
Ilya

Reputation: 29693

Select row or default row

I have the below columns in my table NAMES:

|-------------|
|    NAMES    |
|-------------|
| NAME | LANG |
|-------------|

There are 3 types of records: lang = 'en', lang = 'ua' and lang = chr(0) (default lang)

Now I should select names by lang with next condition:

  1. If I select by lang and record for this lang exists then it should be returned

  2. If I select by lang and record for this lang is absent then default NAME should be returned (where lang = chr(0))

I generated next query for this, but it looks awful

SELECT 
   NAME 
FROM NAMES WHERE 
   LANG = :lang 
   OR (NOT EXISTS(SELECT * FROM NAMES WHERE LANG = :lang) AND LANG = chr(0))

Is there any better way to implement this? (Oracle DB is used)

Sample data:
1)

|--------------|
|    NAMES     |
|--------------|
| NAME | LANG  |
|--------------|
| Ivan |  ua   |
| Kris | chr(0)|
|--------------|

Search by en returns Kris

2)

|--------------|
|    NAMES     |
|--------------|
| NAME | LANG  |
|--------------|
| Ivan |  ua   |
| Kris | chr(0)|
| Mike |  en   |
|--------------|

Search by en returns Mike

3)

|--------------|
|    NAMES     |
|--------------|
| NAME | LANG  |
|--------------|
| Ivan |  ua   |
|--------------|  

Search by en returns nothing

Upvotes: 2

Views: 180

Answers (2)

psaraj12
psaraj12

Reputation: 5072

Use the decode function to achieve the same

WITH language as 
(select count(1) lang_count from names where lang=:lang)
 SELECT 
 A.NAME 
 FROM NAMES a,language 
 where a.lang=decode(language.lang_count,0,chr(0),:lang);

UPDATE 1:-Edited the answer to solve the second case mentioned by OP.The result is same as the one tried by OP but may be little clear

Upvotes: 3

a1ex07
a1ex07

Reputation: 37364

I think the following should be faster:

SELECT name FROM
(
   SELECT name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY 
    CASE WHEN lang = :lang  THEN 0 ELSE 1 END) AS rn
   FROM names WHERE lang = :lang OR lang = chr(0)   
)
WHERE rn = 1;

Upvotes: 2

Related Questions