Re: Handling NULL values in columns when joining on several tables

fsanchez98_at_cs.com (Fernando Sanchez) wrote in message news:<bd1e3d30.0306280720.25127d71_at_posting.google.com>...
> Hi,
> Is there an expert out there that can help me resolve an issue with a
> query.
>
> I am joining several columns to a PERIOD dimension, which contains the
> date and a period key.
>
> The source table or view, might have date values in the column.
>
> The query looks something like this...
>
> SELECT COUNT(*)
> FROM SRC_OFFSET_FACTS_V OFV,
> PERIOD P1,
> PERIOD P2,
> PERIOD P3,
> PERIOD P4,
> BRANCH BRA,
> DEALER DLR,
> OFFSET_TYPE OT
> WHERE OFV.PAY_DT = P1.FULL_DATE (+) AND
> OFV.PIPE_CLEARED_DT = P2.FULL_DATE (+) AND
> OFV.TRANS_BRANCH_ID = BRA.BRANCH_LE_ID AND
> OFV.DEALER_LE_ID = DLR.DEALER_LE_ID AND
> OFV.NSF_DT = P3.FULL_DATE (+) AND
> OFV.NSF_CLEARED_DT = P4.FULL_DATE (+) AND
> (OFV.OFFSET_TYPE = OT.OFFSET_TYPE AND
> OFV.OFFSET_REASON = OT.OFFSET_REASON);
>
> When I run this query, I don't get back all the rows from source view.
> I want to bring back all rows even if the date is a null. There is a
> dumming record in the PERIOD dimension, which has a NULL value in the
> date field. I am using this dumming record as the default key for rows
> that don't have a date values.
>
> Regards,
> Fernando

Not quite sure what you meant.
The outer join alone will get you all the rows from SRC_OFFSET_FACTS_V regardless of the NULL in either SRC_OFFSET_FACTS_V or the period table. The other conditions with the other 2 tables may eliminate rows from the outer join, of course. So, you may not get all the rows from SRC_OFFSET_FACTS_V. If you are thinking of matching up the NULL in SRC_OFFSET_FACTS_V with the NULL in the period table, that will never work. NULL=NULL will always evaluate to false.

SQL> select 1 from dual where null=null;

no rows selected

SQL> select 1 from dual where null is null;

         1

         1

SQL>

• Jusung Yang