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

Fernando Sanchez (fsanchez98_at_cs.com) wrote:
: 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
uhhm... cae of "where something compares-to something-else"

if either one may be null then you *must* explicitly test for that.

Each "compares-to" must be considered as to how the nulls affect it, and then null logic must be added to handle it. (Sometimes the correct logic is to do nothing so the nulls are simply skipped.)

If nulls are involved then the two general strategies are either

        NVL(val1,sensible-default) compares-to NVL(val2,sensible-default)

or

	( val1 compares-to val2 or
          val1 is null and val2 -something- or
	  val2 is null and val1 -something-
	)