Larry,
I confirm your results under latest RBwin6.5++(patch 2).
As you have said, the second SELECT (which produces incorrect results) uses
an expression in the "ON" clause of the OUTER JOIN. That is, "ON M.MonthNo
= (IMON(H.HolDate))."
I checked the syntax diagram. It clearly shows that a column is required.
So it seems that such a construct is not permitted, and therefore the "bug"
is that R:BASE produces a result even though the command should not be
permitted.
However, the second SELECT will work correctly if you add:
WHERE M.MonthNo = H.HolMonthNo
But it will still not work correctly if, instead, you add:
WHERE M.MonthNo = (IMON(H.HolDate))
---------------------------------------------------------------------------
Eric M. Bienstock, Ph.D.
Database Development, etc.
Authorized R:BASE Developer
Eric M. Bienstock & Associates, Inc.
day: 212-978-7948 eve: 973-763-8650(8)
FAX: 209-882-9250 [EMAIL PROTECTED]
http://members.home.net/ericmb/Ericmb.htm
---------------------------------------------------------------------------
----- Original Message -----
From: "Lawrence Lustig" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 07, 2002 2:03 PM
Subject: Possible problem with OUTER JOIN
| Hello all.
|
| I was going to post this to RDCC, but I figured I better get somone to
check
| me out first to see if there really is a problem.
|
| It seems to me that there is a problem with OUTER JOINS returning
incorrect
| data if an expression is used to join the two table. I can believe that
| R:Base might not be able to use an expression in this case and might
return
| a "Can't use expression to join tables" error, but what's happening is
that
| results are being returned that appear to me to be incorrect.
|
| The code below will create two tables (MONTHS and HOLIDAYS) and perform
two
| different selects which, I believe, should return the same result sets.
On
| my system (RBWin 6.5++) they produce different results.
|
| I'd appreciate it if some folks could look it over and try it out and let
me
| know:
|
| 1. Am I correct in believing the result sets should be the same?
|
| 2. Are the result sets different on your system?
|
| (Sorry for the wacky formatting on this message, I did cut and paste from
| R:Code and it seems to have confused Outlook Express).
|
| CREATE TABLE Months (MonthNo INT, MonthName TEXT 10)
| INSERT INTO Months VALUES (1, 'January')
| INSERT INTO Months VALUES (2, 'February')
| INSERT INTO Months VALUES (3, 'March')
| INSERT INTO Months VALUES (4, 'April')
| INSERT INTO Months VALUES (5, 'May')
| INSERT INTO Months VALUES (6, 'June')
| INSERT INTO Months VALUES (7, 'July')
| INSERT INTO Months VALUES (8, 'August')
| INSERT INTO Months VALUES (9, 'September')
| INSERT INTO Months VALUES (10, 'October')
| INSERT INTO Months VALUES (11, 'November')
| INSERT INTO Months VALUES (12, 'December')
|
| CREATE TABLE Holidays +
| (HolDate DATE, HolName TEXT 20, HolMonthNo=(IMON(HolDate)) INT)
| INSERT INTO Holidays VALUES (12/25/02, 'Christmas Day')
| INSERT INTO Holidays VALUES (01/01/02, 'New Year''s Day')
|
| WRITE 'This SELECT has correct results'
| SELECT M.MonthName, H.HolName, M.MonthNo, H.HolMonthNo, (IMON(H.HolDate))
+
| FROM Months M LEFT OUTER JOIN Holidays H ON M.MonthNo = H.HolMonthNo
|
| WRITE 'This SELECT should be the same, but has different results'
| SELECT M.MonthName, H.HolName, M.MonthNo, H.HolMonthNo, (IMON(H.HolDate))
+
| FROM Months M LEFT OUTER JOIN Holidays H ON M.MonthNo = (IMON(H.HolDate))
|
| --
|
| Larry Lustig
|
|
|
|
| _________________________________________________________
| Do You Yahoo!?
| Get your free @yahoo.com address at http://mail.yahoo.com
|
| ================================================
| TO SEE MESSAGE POSTING GUIDELINES:
| Send a plain text email to [EMAIL PROTECTED]
| In the message body, put just two words: INTRO rbase-l
| ================================================
| TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
| In the message body, put just two words: UNSUBSCRIBE rbase-l
| ================================================
| TO SEARCH ARCHIVES:
| http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
