Outer join/build-in functions bug 2007-06-14 - By Vlad Arkhipov
Back
<mailto:maxdb@(protected)>DROP TABLE GIS2.Invests
/
CREATE TABLE GIS2.Invests(
ID INTEGER NOT NULL PRIMARY KEY,
VAL VARCHAR(10) NOT NULL
)
/
DROP TABLE GIS2.Revisions
/
CREATE TABLE GIS2.Revisions(
INVEST_ID INTEGER,
TO_DATE DATE,
FOREIGN KEY (INVEST_ID)
REFERENCES GIS2.Invests (ID)
)
/
INSERT INTO GIS2.Invests (ID, VAL)
VALUES (1, 'ABC')
/
INSERT INTO GIS2.Invests (ID, VAL)
VALUES (2, 'DEF')
/
INSERT INTO GIS2.Revisions (INVEST_ID, TO_DATE)
VALUES (1, '2000-01-01')
/
INSERT INTO GIS2.Revisions (INVEST_ID, TO_DATE)
VALUES (2, NULL)
SELECT i.VAL, SUBSTR(i.VAL, 1, 1)
FROM GIS2.Invests i,
GIS2.Revisions r
WHERE i.ID = 2
AND i.ID = r.INVEST_ID (+)
AND (r.TO_DATE (+) IS NULL OR r.TO_DATE >= DATE)
VAL EXPRESSION1
-- --- -- ---- ------
DEF D
SELECT i.VAL
FROM GIS2.Invests i,
GIS2.Revisions r
WHERE i.ID = 1
AND i.ID = r.INVEST_ID (+)
AND (r.TO_DATE (+) IS NULL OR r.TO_DATE >= DATE)
VAL
-- ---
ABC
SELECT i.VAL, SUBSTR(i.VAL, 1, 1)
FROM GIS2.Invests i,
GIS2.Revisions r
WHERE i.ID = 1
AND i.ID = r.INVEST_ID (+)
AND (r.TO_DATE (+) IS NULL OR r.TO_DATE >= DATE)
returns no records. You may change SUBSTR with any other function to get
the same result.
MaxDB 7.6.00.16 - 016-123-109-428
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
