Larry,
Checkout RSYNTAX.COM for OUTER JOIN. The syntax for the
ON clause is ON lefttblview.column1 = rightblview.column2.
Alternatively you could use ON corr_name.column1 = corr_name.column2.
Options
.column1
Defines the column on which to link.
.column2
Defines the column on which to link.
lefttblview
Explicitly defines the column on which to link-specify the
left table name or view.
righttblview
Explicitly defines the column on which to link-specify the
right table name or view.
The syntax does not permit expressions in the ON clause.
Thus you second example is incorrect and RBASE is correct
in giving you that error message.
For what it's worth I have however been able to use AND in
constructing an ON clause that works. For example
SELECT ('FC' & T1.FPath + T1.FName + '.' + T1.FType & T2.FPath
+ T2.FName + +
'.' + T2.FType & '>>' & .vDifName)=200 FROM &vTableName
T1 LEFT OUTER +
JOIN JRBItem1CD T2 ON (T1.FName = T2.FName AND T1.FType
= T2.FType +
AND T1.FSize = T2.FSize AND T1.FDate = T2.FDate AND T1.FTime
= T2.FTime) +
WHERE T1.FOnMstrCd = 1
--
Jim Bentley
American Celiac Society
[EMAIL PROTECTED] - email
(973) 776-3900 x5029 - voicemail/fax
---- "Lawrence Lustig" <[EMAIL PROTECTED]> 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/
>
__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.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/
