Handling NULL values in columns when joining on several tables

From: Fernando Sanchez <fsanchez98_at_cs.com>
Date: 28 Jun 2003 08:20:18 -0700
Message-ID: <bd1e3d30.0306280720.25127d71@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 Received on Sat Jun 28 2003 - 10:20:18 CDT