I am running a query using the Oracle & Internal SQL modes on version
7.4.3.30 that should return results, but it is not. Below is an example.
Is this a bug or intentional? If it is intentional, can you please give me
an explaination why it works this way. Thanks for you time.
Here are the table DDL and insert statements for the example. The example
assumes you have a schema named EXAMPLE
CREATE TABLE "EXAMPLE"."M1"
(
"OID_M1" Integer NOT NULL,
"OID_S" Integer,
"FLAG_DEL" Char (1) ASCII NOT NULL DEFAULT 'F',
PRIMARY KEY ("OID_M1"),
CONSTRAINT "CK_M1_FLAG_DEL" CHECK "FLAG_DEL" IN ('F','T')
)
CREATE TABLE "EXAMPLE"."M2"
(
"OID_M2" Integer NOT NULL,
"OID_M1" Integer NOT NULL,
"FLAG_DEL" Char (1) ASCII NOT NULL DEFAULT 'F',
PRIMARY KEY ("OID_M2"),
CONSTRAINT "CK_M2_FLAG_DEL" CHECK "FLAG_DEL" IN ('F','T')
)
CREATE TABLE "EXAMPLE"."S"
(
"OID_S" Integer NOT NULL,
"FLAG_DEL" Char (1) ASCII NOT NULL DEFAULT 'F',
PRIMARY KEY ("OID_S"),
CONSTRAINT "CK_S_FLAG_DEL" CHECK "FLAG_DEL" IN ('F','T')
)
INSERT INTO "EXAMPLE"."M1" (OID_M1,OID_S) VALUES (1,2)
INSERT INTO "EXAMPLE"."M1" (OID_M1,OID_S) VALUES (3,4)
INSERT INTO "EXAMPLE"."M1" (OID_M1,OID_S) VALUES (5,6)
INSERT INTO "EXAMPLE"."M1" (OID_M1,OID_S) VALUES (7,8)
INSERT INTO "EXAMPLE"."M2" (OID_M2,OID_M1) VALUES (9,1)
INSERT INTO "EXAMPLE"."M2" (OID_M2,OID_M1) VALUES (10,3)
INSERT INTO "EXAMPLE"."M2" (OID_M2,OID_M1) VALUES (11,5)
INSERT INTO "EXAMPLE"."M2" (OID_M2,OID_M1) VALUES (12,7)
INSERT INTO "EXAMPLE"."S" (OID_S) VALUES (2)
INSERT INTO "EXAMPLE"."S" (OID_S) VALUES (4)
INSERT INTO "EXAMPLE"."S" (OID_S) VALUES (6)
INSERT INTO "EXAMPLE"."S" (OID_S) VALUES (8)
This is the SQL statement that does not return results.
SELECT *
FROM EXAMPLE.M1, EXAMPLE.M2, EXAMPLE.S
WHERE M1.OID_M1 = M2.OID_M1
AND M1.OID_S = S.OID_S(+)
AND M1.FLAG_DEL <> 'T'
AND M2.FLAG_DEL <> 'T'
Below are several variations of the same query that do produce the results
I expect.
SELECT *
FROM EXAMPLE.M1, EXAMPLE.M2, EXAMPLE.S
WHERE M1.OID_M1 = M2.OID_M1
AND M1.OID_S = S.OID_S(+)
AND M2.FLAG_DEL <> 'T'
AND M1.FLAG_DEL <> 'T'
SELECT *
FROM EXAMPLE.M1, EXAMPLE.M2, EXAMPLE.S
WHERE M1.OID_M1 = M2.OID_M1
AND M2.FLAG_DEL <> 'T'
AND M1.OID_S = S.OID_S(+)
AND M1.FLAG_DEL <> 'T'
SELECT *
FROM EXAMPLE.M1 LEFT OUTER JOIN EXAMPLE.S on M1.OID_S = S.OID_S, EXAMPLE.M2
WHERE M1.OID_M1 = M2.OID_M1
AND M1.FLAG_DEL <> 'T'
AND M2.FLAG_DEL <> 'T'
SELECT *
FROM EXAMPLE.M1, EXAMPLE.M2, EXAMPLE.S
WHERE M1.OID_M1 = M2.OID_M1
AND M2.FLAG_DEL <> 'T'
AND M1.FLAG_DEL <> 'T'
AND M1.OID_S = S.OID_S(+)
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
