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.
