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 <mailto:[email protected]> [email protected] _____ From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Friday, October 01, 2010 10:19 AM To: RBASE-L Mailing List Subject: [RBASE-L] - LEFT OUTER JOIN puzzler I'm officially confused. I've created this view: DROP VIEW TEMPVIEW -- 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_NO = T2.PRDY_STCKCRD_NO AND T1.STCD_DASH = T2.PRDY_STCKCRD_DA_ID There is one-to-one relationship on these tables based on common values as shown in the WHERE statement., First test: SELECT COUNT(STCD_NO) FROM TEMPVIEW COUNT (STC ---------- 2800 OK so far. The NON_HFR_SHIP table has 2800 records. My intent is to account for all records. Second test: SELECT COUNT(STCD_NO) FROM TEMPVIEW WHERE RDY_DATE IS NOT NULL COUNT (STC ---------- 2743 So far so good. There are 2743 matches between the two tables. Third test: SELECT COUNT(STCD_NO) FROM TEMPVIEW WHERE RDY_DATE IS NULL COUNT (STC ---------- 2800 This is the one I do not understand. The only difference is whether I specify IS NOT NOT vs. NULL. Why doesn't the third test return 57? If I manually view the results there are 57 records in NON_HFR_SHIP that do not have matches in CW_PROJRDY. This one has me stumped. Thanks for any insights. Mike Ramsour 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.
