Hi Larry, The first select (no expression) returns nulls for non-holiday months (H.Holmonth and Expression column), the second returns 1's under 6.5+
Ben Petersen On 7 Feb 2002, at 14:03, Lawrence Lustig wrote: > 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/
