Hi, raymond99-ga:
You posted a "MySQL query" as follows:
SELECT DISTINCT Things.*
FROM Things
left join Locks as L1 on (Things.id = L1.id AND L1.group = 'user')
left join Locks as L2 on (Things.id = L2.id AND L2.group =
'brand')
left join Locks as L3 on (Things.id = L3.id AND L3.group =
'color')
WHERE IfNull(L1.lock in ('john','pete'),1)
AND IfNull(L2.lock in ('ford','mazda'),1)
AND IfNull(L3.lock in ('red','green'),1);
The first part of this SELECT statement will actually work fine in
Oracle, but we run into trouble with the phrasing of the WHERE
condition. Most obvious is the IfNull function, which doesn't exist
in Oracle.
What does the IfNull function do in MySQL? Well, it tests whether the
first argument is Null or not. If the first argument is Null, then
the function returns the value of the second argument. However if the
first argument is not Null, then that first argument is the value
returned by this function. See here:
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
[MySQL IfNull]
http://www.mysql.com/doc/en/Control_flow_functions.html
IFNULL(expr1,expr2)
"If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2."
[MySQL IN Operator]
http://www.mysql.com/doc/en/Comparison_Operators.html
(about halfway down the page)
expr IN (value,...)
"Returns 1 if expr is any of the values in the IN list, else returns
0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done using a binary search. This means IN is very quick if the IN
value list consists entirely of constants. If expr is a case-sensitive
string expression, the string comparison is performed in
case-sensitive fashion:
mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
-> 1
"The number of values in the IN list is only limited by the
max_allowed_packet value.
"From 4.1 (to comply with the SQL-99 standard), IN returns NULL not
only if the expression on the left hand side is NULL, but also if no
match is found in the list and one of the expressions in the list is
NULL."
[MySQL Working with NULL]
http://www.mysql.com/doc/en/Working_with_NULL.html
"Note that in MySQL, 0 or NULL means false and anything else means
true. The default truth value from a boolean operation is 1."
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Exactly the same functionality as MySQL's IfNull is provided in Oracle
by the NVL function. Again, NVL takes two arguments and depending on
whether the first of these is Null or not, the function returns the
second argument or the first argument, respectively:
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
[Oracle 9i NVL]
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions88a.htm#91654
NVL ( expr1 , expr2 )
"If expr1 is null, then NVL returns expr2. If expr1 is not null, then
NVL returns expr1."
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
But it's not quite enough if we just replace the IfNull function calls
by NVL function calls, because the original query is taking advantage
of the MySQL treatment of expressions as being Boolean "true" if they
are nonzero. Oracle SQL per se has no Boolean datatype (although
there is one in PL/SQL), and the Boolean value of "control"
expressions is always an implicit matter. In particular there is no
implied equivalence of numeric 1 with Boolean "true" in Oracle SQL.
We need to replace the IfNull functions that work so well in MySQL
with a compound logical expression in Oracle, one which tests the
alternatives of some "missing" Locks.lock value being Null (because of
the outer join condition) versus an actual value that does appear in
the lists of literal strings.
Therefore the minimum amount of conversion that would need to be done
to your query is this:
SELECT DISTINCT Things.*
FROM Things
left join Locks as L1 on (Things.id = L1.id AND L1.group = 'user')
left join Locks as L2 on (Things.id = L2.id AND L2.group =
'brand')
left join Locks as L3 on (Things.id = L3.id AND L3.group =
'color')
WHERE ( L1.lock Is Null OR L1.lock in ('john','pete') )
AND ( L2.lock Is Null OR L2.lock in ('ford','mazda') )
AND ( L3.lock Is Null OR L3.lock in ('red','green') );
Below are more supporting Web references to the Oracle syntax
documentation. If you should require further clarification of this
answer, please use the button provided to request it.
In particular the syntax given in this simplified example "hard codes"
the lock groups and specific "keys". If you like, I can make some
suggestions about ways to structure the code when the "keys" held by a
user are table-driven.
regards, mathtalk-ga
[Oracle 9i DISTINCT Clause of SELECT]
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065826
DISTINCT | UNIQUE
Specify DISTINCT or UNIQUE if you want Oracle to return only one copy
of each set of duplicate rows selected (these two keywords are
synonymous). Duplicate rows are those with matching values for each
expression in the select list.
[Oracle 9i PL/SQL Boolean Literals]
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/02_funds.htm#2924
Boolean Literals
Boolean literals are the predefined values TRUE, FALSE, and NULL
(which stands for a missing, unknown, or inapplicable value).
Remember, Boolean literals are values, not strings. For example, TRUE
is no less a value than the number 25.
[Oracle 9i IS NULL]
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems20.htm#34133
"IS NULL"
"This comparison operator returns the Boolean value TRUE if its
operand is null, or FALSE if its operand is not null."
[Oracle 9i IN]
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems20.htm#34124
"IN"
"This comparison operator tests set membership. It means "equal to any
member of." The set can contain nulls, but they are ignored. Also,
expressions of the form
value NOT IN set
yield FALSE if the set contains a null."
[Oracle 9i NVL]
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions88a.htm#91654
NVL ( expr1 , expr2 )
Purpose
"NVL lets you replace a null (blank) with a string in the results of a
query. If expr1 is null, then NVL returns expr2. If expr1 is not null,
then NVL returns expr1. The arguments expr1 and expr2 can have any
datatype. If their datatypes are different, then Oracle converts expr2
to the datatype of expr1 before comparing them.
"The datatype of the return value is always the same as the datatype
of expr1, unless expr1 is character data, in which case the return
value's datatype is VARCHAR2 and is in the character set of expr1."
[Oracle 9i Left Outer Join Example]
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2107297
"The following example uses a left outer join to return the names of
all departments in the sample schema hr, even if no employees have
been assigned to the departments:
SELECT d.department_id, e.last_name
FROM departments d LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
ORDER BY d.department_id;
[Oracle 9i Nulls in Conditions]
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements5a.htm#4030
[Oracle 9i NVL2]
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions89a.htm#84586
NVL2 ( expr1 , expr2 , expr3 )
Purpose
"NVL2 lets you determine the value returned by a query based on
whether a specified expression is null or not null. If expr1 is not
null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns
expr3. The argument expr1 can have any datatype. The arguments expr2
and expr3 can have any datatypes except LONG.
"If the datatypes of expr2 and expr3 are different, then Oracle
converts expr3 to the datatype of expr2 before comparing them unless
expr3 is a null constant. In that case, a datatype conversion is not
necessary.
"The datatype of the return value is always the same as the datatype
of expr2, unless expr2 is character data, in which case the return
value's datatype is VARCHAR2."
[Oracle 9i NULLIF]
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions85a.htm#SQLRF00681
"NULLIF compares expr1 and expr2. If they are equal, then the function
returns null. If they are not equal, then the function returns expr1.
You cannot specify the literal NULL for expr1.
"The NULLIF function is logically equivalent to the following CASE
expression:
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END" |
Clarification of Answer by
mathtalk-ga
on
25 Sep 2003 05:34 PDT
Hi, raymond99-ga:
You are correct that the outer join syntax in Oracle 8 is nonstandard:
[Oracle 8: Outer Joins]
http://www.isse.gmu.edu/~jics/outerJoin.html
"Oracle does not recognize outer join SQL-92 syntax instead it has its
own rules."
I think the time has come for me to suggest a form for your query that
doesn't rely on outer joins. Not only does this avoid a potential for
Oracle version dependence, it should actually improve performance
_and_ give you a flexible way to specify the keys available for a
particular user or set of users.
So let's back up and recount the given facts of your situation. On
the one hand you have some "Things" records and associated with them
(through a foreign key relationship), some "Locks" records. Those
were well described by your definitions of "Table #1" and "Table #2."
CREATE TABLE Things (
id integer PRIMARY KEY,
name varchar(30)
);
CREATE TABLE Locks (
id integer,
group varchar(30),
lock varchar(30)
);
What should presumably also be defined is a table to hold the "keys"
belonging to various users. In your original query the specific
group/key pairs were put in "by hand". Presumably in any "real"
version of such a query you will need the conditions to be "table
driven." In order to discuss this approach I will define "Table #3"
like this:
CREATE TABLE HoldsKey (
holder integer,
group varchar(30),
lock varchar(30)
);
As you will see, one can certainly substitute particular values for
the records that might be returned from HoldsKey. But if a general
approach is desired for determining, given a holder of certain keys,
what Things that holder might access, then the advantages of
maintaining such keys in a table of their own should be evident.
[Note: I deliberately chose to label the first field of this new
table as "holder" rather than "user" to avoid any confusion with the
group called "user" in your sample data.]
Next let us state the logical condition that corresponds to a "holder"
having access to a "thing":
A specific holder H has access to a specific thing T if and only if
for every group G in the Locks records that apply to T, there exists a
group/lock pair (G,L) in the HoldsKey records that apply to H such
that the same group/lock pair appears in the Locks records that apply
to T.
Translating such a logical condition into SQL is complicated in this
case by the lack of a direct "for every" construct in SQL. However we
know that "there exists" is represented in SQL by the EXISTS keyword
construct, and there is a logical equivalence between "for every" and
"there exists" that can be expressed using two logical negations, an
"inner" one and an "outer" one:
For every x, P(x) is true.
means the same thing as:
There does not exist x such that P(x) is false.
With this hint we can give a SQL query that expresses the logical
condition above:
SELECT * FROM Things
WHERE NOT EXISTS (SELECT * FROM Locks L1
WHERE L1.id = Things.id
AND NOT EXISTS (SELECT * FROM HoldsKey, Locks L2
WHERE L2.id = L1.id
AND L2.group = L1.group
AND HoldsKey.holder = h
AND HoldsKey.group = L2.group
AND HoldsKey.lock = L2.lock
)
)
;
where h is a "parameter" that represents a particular holder of keys.
To show that one can still "hard code" the holder's keys, here is an
equivalent to the original example:
SELECT * FROM Things
WHERE NOT EXISTS (SELECT * FROM Locks L1
WHERE L1.id = Things.id
AND NOT EXISTS (SELECT * FROM Locks L2
WHERE L2.id = L1.id
AND L2.group = L1.group
AND ( (L2.group = 'user' AND L2.lock IN ('john', 'pete'))
OR (L2.group = 'brand' AND L2.lock in ('ford', 'mazda'))
OR (L2.group = 'color' AND L2.lock in ('red', 'green'))
)
)
)
;
Below is some "test case" data, based on your examples:
======================================================
INSERT INTO Things
VALUES (1,'Unlocked1');
INSERT INTO Things
VALUES (2,'Unlocked2');
INSERT INTO Things
VALUES (3,'Unlocked3');
INSERT INTO Things
VALUES (4,'NotUnlocked4');
INSERT INTO Things
VALUES (5,'NotUnlocked5');
INSERT INTO Things
VALUES (6,'AlwaysUnlocked6');
INSERT INTO Locks
VALUES (1,'user','john');
INSERT INTO Locks
VALUES (1,'brand','ford');
INSERT INTO Locks
VALUES (2,'user','pete');
INSERT INTO Locks
VALUES (2,'color','red');
INSERT INTO Locks
VALUES (3,'brand','mazda');
INSERT INTO Locks
VALUES (4,'user','john');
INSERT INTO Locks
VALUES (4,'brand','kia');
INSERT INTO Locks
VALUES (4,'color','red');
INSERT INTO Locks
VALUES (5,'user','john');
INSERT INTO Locks
VALUES (5,'type','specialty');
=====================================
regards, mathtalk-ga
|
,
0 Comments
)