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 (AT) durchholz (DOT) org <jo (AT) durchholz (DOT) org> wrote:
naiive answer

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.

On 07.01.2009 17:00, jo (AT) durchholz (DOT) org wrote:
IMHO this is something typically solved in application land - not in the
database. Do the query, if it returns an empty list, use the user name.

Note that the fact that it appears twice does not necessarily mean that
a database has to do double work. After all SQL is declarative and the
DB engine is free to get at the data in any way.

Kind regards

robert


--
remember.guy do |as, often| as.you_can - without end

<jo (AT) durchholz (DOT) org> wrote

You may not have declared any primary key. However, it sounds from your
description as though (Alias,User) is a candidate key, and the only
candidate.
You might just as well consider it the primary key. And, yeah, in this case
the primary key is the whole table.


what's wrong with WITH?

Jo,

Not quite sure if this is what you mean:

-- This table is not even in first NF
-- As per the OP, it should model a subquery result
-- Suppose, this is a LEFT OUTER JOIN query which
-- has always the user_full_name and just a user_alias when existing
CREATE TABLE Aliases
(user_full_name VARCHAR(15) NOT NULL,
user_alias VARCHAR(15) NULL);

INSERT INTO Aliases VALUES('Mickey Mouse', 'Mousy');
INSERT INTO Aliases VALUES('Mickey Mouse', 'Mick');
INSERT INTO Aliases VALUES('Mickey Mouse', 'Mega Mouse');
INSERT INTO Aliases VALUES('Donald Duck', NULL);
INSERT INTO Aliases VALUES('Dagobert Duck', 'Mr. Money');
INSERT INTO Aliases VALUES('Mini Mouse', 'Mousy');

SELECT *
FROM (SELECT user_full_name,
user_alias,
CASE WHEN user_alias IS NULL
THEN user_full_name
ELSE user_alias
END AS alias_or_user
FROM Aliases) AS A
WHERE A.alias_or_user = 'Mousy'; -- use a parameter here

I renamed the 'user' column as 'user' is a reserved word - might be
not in Oracle though, but I do not have Oracle installed.

brgds

Philipp Post