Best way to return a default record if a query returns an ..

The subject says it all, here's an example:

Table "aliases" with fields "alias" and "user" (real name). No primary
keys on the table, an alias may refer to multiple users and a user my
have multiple aliases (remember this is just an example, in a real-
world example the "aliases" table would be a subquery).
I want to write a query that will return a series of user names that a
given alias maps to, or the user name itself if there's no alias.

The best I could come up with was this:

(SELECT user FROM aliases WHERE alias = :alias)
UNION
(
SELECT :alias FROM DUAL
WHERE NOT EXISTS (
SELECT * FROM aliases WHERE alias = :alias))

The downside here is that "aliases" appears twice, and in the real
world, it could be a subquery which may be (a) potentially expensive,
(b) lead to an exponential blow-up if this construct is nested, and
(c) could lead to maintenance problems.
So is there a way to rewrite the above to refer to the aliases table
just once?

FWIW I'm on Oracle, and while I know all about WITH, I don't trust it
very far, because it tends to break query plan display (hangs if the
subquery name is used more than once) and the ODBC driver (causes
Oracle to throw up with ORA-00600 (assertion failure in the database
engine) if the query gets a bit complex).
Besides, I'd like to learn techniques that will work with other
databases, too

Regards,
Jo

On 2009-01-07, jo.DeleteThis@durchholz.org wrote:
> The subject says it all, here's an example:

naiive answer

> Table "aliases" with fields "alias" and "user" (real name). No primary
> keys on the table, an alias may refer to multiple users and a user my
> have multiple aliases (remember this is just an example, in a real-
> world example the "aliases" table would be a subquery).
> I want to write a query that will return a series of user names that a
> given alias maps to, or the user name itself if there's no alias.
>
> The best I could come up with was this:
>
> (SELECT user FROM aliases WHERE alias = :alias)
> UNION
> (
> SELECT :alias FROM DUAL
> WHERE NOT EXISTS (
> SELECT * FROM aliases WHERE alias = :alias))

that makes no sense at all.

I could guess what you are trying to express
but then I might answer the wrong question.
check line 3.

have you considered using a stored procedure?
I use them all the time in postgres.