Javier: Thanks for responding. I tried your suggestions but I got the same results.
This is using the latest version of Turbo V-8. The STCD_NO values are integer data types and the STCD_DASH/PRDY_STCKCRD_DA_ID are TEXT 12. The relationship between the two tables based on STCD_NO and PRDY_STCKCRD_NO is many to many. The WHERE T1.STCD_DASH = T2.PRDY_STCKCRD_DA_ID then makes it one to one. As far as SELECTing and BROWSEing the view appears to work as intended. It's strange, though, that doing the COUNT(*) WHERE RDY_DATE IS NOT NULL gives the correct answer (2,743) whereas COUNT(*) WHERE RDY_DATE IS NULL returns the count for the entire dataset (2,800) instead of 57. It's a real head scratcher. Thanks for contributing and have a wonderful weekend. -- Mike From: "Javier Valencia" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: 10/01/2010 03:04 PM Subject: [RBASE-L] - RE: LEFT OUTER JOIN puzzler Sent by: [email protected] Mike, I was just playing with outer joins last night and had some issues that I eventually resolved. One of them was that the link between tables is the clause after the ?ON? and the clause after the WHERE is repetitive and likely creating the problem, Try the following: CREATE TEMPORARY VIEW TEMPVIEW (STCD_NO,STCD_DASH,SHIPDATE,RDY_DATE,POUNDS ) + AS SELECT T1.STCD_NO,T1.STCD_DASH,T1.SHIPDATE,T2.PRDY_INIT_READY_DT,T1. POUNDS + FROM NON_HFR_SHIP T1 LEFT OUTER JOIN CW_PROJRDY T2 + ON T1.STCD_NO = T2.PRDY_STCKCRD_NO I that works, try the following: CREATE TEMPORARY VIEW TEMPVIEW (STCD_NO,STCD_DASH,SHIPDATE,RDY_DATE,POUNDS ) + AS SELECT T1.STCD_NO,T1.STCD_DASH,T1.SHIPDATE,T2.PRDY_INIT_READY_DT,T1. POUNDS + FROM NON_HFR_SHIP T1 LEFT OUTER JOIN CW_PROJRDY T2 + ON T1.STCD_NO = T2.PRDY_STCKCRD_NO + WHERE T1.STCD_DASH = T2.PRDY_STCKCRD_DA_ID Javier, Javier Valencia, PE 913-829-0888 Office 913-915-3137 Cell 913-649-2904 Fax [email protected] Confidentiality Notice This message is intended exclusively for the individual or entity to which it is addressed and may contain privileged, proprietary, or otherwise private information. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message. Confidentiality Notice This message is intended exclusively for the individual or entity to which it is addressed and may contain privileged, proprietary, or otherwise private information. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message.
