Select row or default row

Question

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

I'm confused, do you want the lang returned when it's not null, and the name returned when it is, or name returned everytime? Also how can it return a name if record doesn't exist?... None of the solutions provided only return name, which is what your query is doing... I guess I need to see expected results when each type is used... and sample data to truly understand. — xQbert, Oct 06 '14 at 15:07
You should really add a little sqlfiddle (or some creation code with desired output) to clarify your needs, the answers seems to indicate that there's some confusion... — Raphaël Althaus, Oct 06 '14 at 15:14
@Ilya so it's not possible to have two times `Ivan` in names, one time with `chr(0)` and another time with `ua` for example ? Name is always unique ? — Raphaël Althaus, Oct 06 '14 at 15:26
@RaphaëlAlthaus name isn't unique. It possible to have `Ivan` two times — Ilya, Oct 06 '14 at 15:32
@ llya I believe @psaraj12 has provided the simplest answer that works. — xQbert, Oct 06 '14 at 15:36
@xQbert I would say no, but may be wrong : this will return two rows if theres's `Ivan / en` and `Ivan / chr(0)`. I think that in this case, only one value should be returned. But with a distinct (if only Name is needed, this would be ok). — Raphaël Althaus, Oct 06 '14 at 15:40
See this sqlFiddle : http://sqlfiddle.com/#!4/1843a1/8 to see what's op wants (first query) and what the different answers return. — Raphaël Althaus, Oct 06 '14 at 15:45
@Raphael if the input of language in a particular order is important then i agree — psaraj12, Oct 06 '14 at 15:47
@psaraj12 well, seems that I misunderstood something : you have to check ON ALL ROWS if there's any entry with given language. If that's true, DON'T RETURN the rows with only chr(0). See sample 2 => Don't return Kris. — Raphaël Althaus, Oct 06 '14 at 15:49
@Raphael i understood your point with the sql fiddle ,will try to check for a solution for case 2 — psaraj12, Oct 06 '14 at 16:15

psaraj12 · Answer 1 · 2014-10-07T05:53:07.730

3

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

edited Oct 07 '14 at 05:53

answered Oct 06 '14 at 14:54

psaraj12

4,772
2
21
30

Isn't chr(0) just NULL in Oracle? Decode could be an alternative, but why to reinvent the wheel? – Lalit Kumar B Oct 06 '14 at 14:59
1

@LalitKumarB: No, it is NOT the same as NULL. Please read the docs before jumping to conclusions. :-) – toddlermenot Oct 06 '14 at 15:16
@toddlermenot, I might be incorect per you. Could you please show how. I might learn something new and get back to docs. Been a long time indeed. – Lalit Kumar B Oct 06 '14 at 15:24
@LalitKumarB: Just execute these two queries and observe the result ( chr(0) is NOT treated like NULL ): SELECT * FROM DUAL WHERE CHR(0)=CHR(0); select * from dual where null=null; – toddlermenot Oct 06 '14 at 15:26
NULL = NULL is simply undetermined state. A third state perhaps? It is an incorrect comparision. – Lalit Kumar B Oct 06 '14 at 15:27
Precisely. Per your statement, both queries should yield the same results which it doesn't. – toddlermenot Oct 06 '14 at 15:29
Wait, are we talking about NULL value or the ASCII? Because I am advocating over zero length string and NOT ASCII of it. – Lalit Kumar B Oct 06 '14 at 15:35
This query will return lang-specific and default rows. (e.g. for 2-nd case from my question, it will return `Mike` and `Kris`) – Ilya Oct 06 '14 at 15:42
@LalitKumarB: I do not know if the recent series of downvotes of my answers is you. But if it is you, I would say that it is uncalled for, despite never downvoting your *wrong* (now deleted) answer. This is not cool and goes against the philosophy of the SO community dude. Be nice. – toddlermenot Oct 06 '14 at 16:49
@toddlermenot, I am only aware of the downvote on my answer, which I later deleted, since I was unable to explain further and found it better to stop further discussion as it was going directionless. I don't think I participated in any other discussions to up/down vote your answers. – Lalit Kumar B Oct 06 '14 at 17:41
@LalitKumarB: Really? SO's vote abuse reversal script has given all my lost points(-20) back and your number of downvotes happen to reduce from 23 yesterday to 13 today(-10) coincidentally? Grow up. How on earth did they made you a moderator at orafaq? And please stop with "I am here to learn" facade. All you seem to like is hide your mistakes and take vengeance on people who point out your mistakes. – toddlermenot Oct 07 '14 at 17:55

score 2 · Answer 2 · answered Oct 06 '14 at 14:53

2

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;

answered Oct 06 '14 at 14:53

a1ex07

36,826
12
90
103

If I am not blind, but it is an overkill. Either `NVL` or even `DECODE` for that matter should suffice the requirement. – Lalit Kumar B Oct 06 '14 at 14:57
1

@ Lalit Kumar B: You're right, I'm just used to `CASE`. I don't think it's gonna be a big difference between `case`/`nvl`/`decode` though in this case. – a1ex07 Oct 06 '14 at 14:59
I would always recommend using `CASE` as opposed to `NVL()` and `DECODE()` @Lalit; [they implicitly](http://stackoverflow.com/q/13712763/458741) [convert](http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions119.htm#SQLRF00684) datatypes, whereas `CASE` and `COALESCE()` don't. I've been caught out a couple of times by this now and it's easier to use the ANSI functions. – Ben Oct 06 '14 at 19:24

Select row or default row

2 Answers2