Hello,

I am trying to build a dimensional model of some data that exists in a single table. Here is the process I've been following and the results I am getting...

A) Create dimension (we'll call it DIM1) from a collection of fields in the source table (we'll call it SOURCE). I am selecting distinct values and also adding a counter(we'll call it DIM1_KEY) Note: some of the records/fields in SOURCE contain NULL values.

B) SOURCE contains 107,000 records and DIM1 contains 3,700 records.

C) Looking at the data in DIM1 you can see that there are NULL values in the same records/fields as in SOURCE.

D) When I do an Inner Join with DIM1 as the host and SOURCE as the cross-reference the resulting data output only has 8,400 records and DIM1_KEY only has 178 distinct values.

E) If I recreate SOURCE and set MISSING=OFF (remove the NULL values) and repeat steps A-D the resulting data output contains the expected 107,000 records with 3,700 distinct values for DIM1_KEY.

My questions are...

1) Is this expected behavior?

2) Removing the NULL values from SOURCE may not be an acceptable solution. Is there a way to configure the join so it will work with the NULL values?

Thanks,

Dan

This message has been edited. Last edited by: Kerry,

Dan

The problem is at the SQL join level. The problem is that null in any side of an expression yields false in SQL.

Thus on an equijoin "null eq null" is always false and thus nulls on either side of the join expression are never returned. This article explains it well.

http://msdn.microsoft.com/en-us/library/ms190409.aspx

The solution

1. code an SQL passthru with an ON expression that eliminates the nulls from both columns

FROM SOURCE T1 JOIN INNER DIM1 T2 ON NVL(T1.DIM1_KEY,99999) = NVL(T2.DIM1_KEY,99999)

This replaces the null values with 99999 using the Oracle function NVL (prob dialect dependent)
and allows the equality test to work.

2. Copy the tables over to wf and use wf join. wf chose the sensible option with null in that it treats it as just another value and so null = null returns true.

The left join argument provided in the Microsoft article is a bit of a red herring in that it suggests falsely that the deliberate reason the inner join does not work is that it is difficult to distinguish the results from a left outer if it does not work this way.

This message has been edited. Last edited by: hammo1j,

John,

This is great. Thanks for the information. I suspect this shall be just what I need to fix the issue.

Regards,

Dan

FYI...

For those using MS SQL the function is

ISNULL ( check_expression , replacement_value )

If check_expression is NULL the function will return replacement_value else it will return check_expression. Note that replacement_value must conform to the same data type as check_expression.

Cheers,

Dan

Here is a further update. ISNULL worked OK in a DevStudio procedure but it does not work in a Data Management Console Data Flow procedure.

The fine folks at tech support suggested I use the COALESCE function instead. That works. In fact, the text editor recognizes it and colors it red.

Here is the syntax:
COALESCE(check_expression,replacement_value)

In my example I used 9876 as the replacement_value. For fields with a numeric format I could simply use 9876 but for fields with a text format I had to put the 9876 is single quotes as '9876'

So,
COALESCE(text_field,'9876') or
COALESCE(numeric_field,9876)

With this function you can actually use as many array items as you want. It simply returns the first one with a Non-NULL value. For example you could use COALESCE(home_phone, cell_phone, 5555555). If home_phone has a value that is what you get, if home_phone is null you get cell_phone, if both are null you get 5555555, etc.

This thread is very interesting, and I was hoping it would solve this exact problem I have with DB2.

LEFT OUTER JOIN BSLC.NR_RSKRT_ASSESS_D T5 ON COALESCE(T5.RRA_SK,99999999) = COALESCE(T2.RRA_SK,99999999) )

I get the same number of rows with or without the COALESCE, when I know I should be getting a 1000 more rows... It also slows down the SQL.

Francis,

Would a subselect help in your instance at all?

T

Makes sense that it might slow down the query since the COALESCE would disable the use of any index on RRA_SK in the foreign table.

Dan, yes, I realize that.

Tony, I was hoping to do this in WebFOCUS instead of SQL, but I still may go the SQL route and perhaps use a sub-select.

Meanwhile, I've been told that, because of a complete lack of business logic integrity, I shouldn't be using this field for the join and use something else, a field that isn't even indexed.

Thanks,

Anyone know why a LEFT OUTER JOIN in WebFOCUS does NOT get translated to a LEFT OUTER JOIN in DB2 SQL???

WebFOCUS:

SQL DB2 SET SQLJOIN OUTER ON
END

JOIN
COMM_CUST_CODED_CRED_HIST.COMM_CUST_KEY IN COMM_CUST_CODED_CRED_HIST TO ALL
COMM_CUST_D.COMM_CUST_KEY IN COMM_CUST_D AS J1
END
JOIN
LEFT_OUTER COMM_CUST_D.APPL_PURP_CD IN COMM_CUST_CODED_CRED_HIST TO
IB_APPL_PURP_C.APPL_PURP_CD IN IB_APPL_PURP_C AS J2
END
JOIN
COMM_CUST_D.RESP_NODE_ID IN COMM_CUST_CODED_CRED_HIST TO ALL
BSL_RESP_D.RESP_NODE_ID IN BSL_RESP_D AS J3
END
JOIN
LEFT_OUTER COMM_CUST_D.PROPOS_OVRD_CD IN COMM_CUST_CODED_CRED_HIST TO
NR_RTG_OVRD_RSN_C.PROPOS_OVRD_CD IN NR_RTG_OVRD_RSN_C AS J4
END
JOIN
LEFT_OUTER COMM_CUST_D.COMM_CUST_KEY IN COMM_CUST_CODED_CRED_HIST TO ALL
NR_RSKRT_ASSESS_D.COMM_CUST_KEY IN NR_RSKRT_ASSESS_D AS J5
END

TABLE FILE COMM_CUST_CODED_CRED_HIST
SUM
MIN.COMM_CUST_D.CUST_NM
MIN.COMM_CUST_D.CUST_APPL_ID
MIN.COMM_CUST_D.UBN_ID
MIN.COMM_CUST_D.CUST_SUB_GUAR_IND
MIN.COMM_CUST_D.C_CUR_RISK_RTG_CD
MIN.COMM_CUST_D.C_PRO_RISK_RTG_CD
MIN.COMM_CUST_D.RM_CD
MIN.COMM_CUST_D.REL_ACCT_MGR_NM
MIN.COMM_CUST_D.APPROV_OFFICER_ID
MIN.COMM_CUST_D.APPROV_OFFICER_NM
MIN.COMM_CUST_D.CUST_GLOB_AUTH_AMT
MIN.COMM_CUST_D.APPL_PURP_CD
MIN.COMM_CUST_D.SRC_BR_TRNST_NUM
MIN.COMM_CUST_D.CUST_NEW_BUS_IND
MIN.NR_RTG_OVRD_RSN_C.PROPOS_OVRD_ENG_DS
MIN.NR_RSKRT_ASSESS_D.OVALL_WGHT_RSKRT_CD AS O_W_RSKRT_CD
MIN.IB_APPL_PURP_C.APPL_PURP_ENG_DS
MIN.COMM_CUST_CODED_CRED_HIST.CCH_ACTIVITY_DT
MIN.BSL_RESP_D.LVL14_RESP_NODE
MIN.BSL_RESP_D.LVL14_RESP_NODE_NM
BY COMM_CUST_D.COMM_CUST_KEY
BY COMM_CUST_D.APPL_DIM_KEY
WHERE COMM_CUST_CODED_CRED_HIST.ACTIVITY_CD EQ 'BC';
WHERE COMM_CUST_CODED_CRED_HIST.CCH_ACTIVITY_DT FROM '2010-02-01' TO '2010-02-28'
WHERE COMM_CUST_D.SRC_BR_TRNST_NUM NE 3964;
WHERE COMM_CUST_D.CUST_ST_DT LE '2010-02-01' AND COMM_CUST_D.CUST_END_DT GE '2010-02-28';
WHERE BSL_RESP_D.RESP_NODE_FR_DT LE '2010-02-01' AND BSL_RESP_D.RESP_NODE_TO_DT GE '2010-02-28';
WHERE (NR_RSKRT_ASSESS_D.RRA_ST_DT LE '2010-02-01' AND NR_RSKRT_ASSESS_D.RRA_END_DT GE '2010-02-28') OR NR_RSKRT_ASSESS_D.RRA_END_DT IS MISSING;
WHERE COMM_CUST_D.SAMU_RESP_CODE EQ '' OR COMM_CUST_D.SAMU_RESP_CODE IS MISSING
ON TABLE HOLD AS R031H002A

Translates to:

SELECT
T2.COMM_CUST_KEY,T2.APPL_DIM_KEY, MIN(T2.CUST_NM),
MIN(T2.CUST_APPL_ID), MIN(T2.UBN_ID),
MIN(T2.CUST_SUB_GUAR_IND), MIN(T2.C_CUR_RISK_RTG_CD),
MIN(T2.C_PRO_RISK_RTG_CD), MIN(T2.RM_CD),
MIN(T2.REL_ACCT_MGR_NM), MIN(T2.APPROV_OFFICER_ID),
MIN(T2.APPROV_OFFICER_NM), MIN(T2.CUST_GLOB_AUTH_AMT),
MIN(T2.APPL_PURP_CD), MIN(T2.SRC_BR_TRNST_NUM),
MIN(T2.CUST_NEW_BUS_IND), MIN(T4.PROPOS_OVRD_ENG_DS),
MIN(T5.OVALL_WGHT_RSKRT_CD), MIN(T3.APPL_PURP_ENG_DS),
MIN(T1.CCH_ACTIVITY_DT), MIN(T6.LVL14_RESP_NODE),
MIN(T6.LVL14_RESP_NODE_NM)
FROM ( ( ( ( ( BSLC.COMM_CUST_CODED_CRED_HIST T1
INNER JOIN BSLC.COMM_CUST_D T2 ON T2.COMM_CUST_KEY = T1.COMM_CUST_KEY )
LEFT OUTER JOIN BSLC.IB_APPL_PURP_C T3 ON T3.APPL_PURP_CD = T2.APPL_PURP_CD )
LEFT OUTER JOIN BSLC.NR_RTG_OVRD_RSN_C T4 ON T4.PROPOS_OVRD_CD = T2.PROPOS_OVRD_CD )
INNER JOIN BSLC.NR_RSKRT_ASSESS_D T5 ON T5.COMM_CUST_KEY = T2.COMM_CUST_KEY )
INNER JOIN BSLC.BSL_RESP_D T6 ON T6.RESP_NODE_ID = T2.RESP_NODE_ID )
WHERE
(T1.CCH_ACTIVITY_DT BETWEEN '2010-02-01' AND '2010-02-28') AND 
(T1.ACTIVITY_CD = 'BC') AND
((T2.SAMU_RESP_CODE IS NULL) OR (T2.SAMU_RESP_CODE = ' ')) AND
(T2.CUST_END_DT >= '2010-02-28') AND (T2.CUST_ST_DT <= '2010-02-01') AND
(T2.SRC_BR_TRNST_NUM <> 3964) AND
(((T5.RRA_ST_DT <= '2010-02-01') AND (T5.RRA_END_DT >= '2010-02-28')) OR T5.RRA_END_DT IS NULL) AND
(T6.RESP_NODE_TO_DT >= '2010-02-28') AND (T6.RESP_NODE_FR_DT <= '2010-02-01')
GROUP BY
T2.COMM_CUST_KEY,T2.APPL_DIM_KEY
ORDER BY
T2.COMM_CUST_KEY,T2.APPL_DIM_KEY
FOR FETCH ONLY;

I am expecting the INNER JOIN BSLC.NR_RSKRT_ASSESS_D T5 to be an OUTER JOIN.

Thanks,

Francis,

Try commenting this out; seems the OUTER turns into an INNER with WHERE tests. Just a thought...

WHERE (NR_RSKRT_ASSESS_D.RRA_ST_DT LE '2010-02-01' AND NR_RSKRT_ASSESS_D.RRA_END_DT GE '2010-02-28') OR NR_RSKRT_ASSESS_D.RRA_END_DT IS MISSING;

Tom, it's definitely the WHERE statement.

• No WHERE gives me OUTER JOIN.
  
• WHERE without the MISSING clause gives me INNER
  [LIST]WHERE with the MISSING clause gives me INNER

I'll have to resort to SQL instead of WebFOCUS.

Thanks,

Hey Francis,

Yep, glad you found it...

Can still use WF; extracting the data while TESTing on the INNER Joins, then TESTing on the HOLD R031H002A file with the other stuff...

2 steps, I know, but...

Tom

Tom, thanks for the suggestion, but I'll have to change a few more things. Since I am doing a SUM, I'll have to change it to a PRINT and then test on the hold file.

Regards,

Just a wild idea. Have you tried putting the offending WHERE condition into a DEFINE and then using the DEFINEd field in the WHERE clause? Hopefully this won't cause any WebFOCUS-based processing.

DEFINE FILE NR_RSKRT_ASSESS_D
 DT_TEST/I1 = IF (NR_RSKRT_ASSESS_D.RRA_ST_DT LE '2010-02-01' AND NR_RSKRT_ASSESS_D.RRA_END_DT GE '2010-02-28')
                  OR (NR_RSKRT_ASSESS_D.RRA_END_DT IS MISSING) THEN 1 ELSE 0 ;
END
-*
TABLE FILE
.
.
 WHERE DT_TEST EQ 1 ;
END

Intriguing idea, I'll give it a try.