Reputation: 29693
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:
If I select by lang and record for this lang exists then it should be returned
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
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
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