One to Many

UDA
Enthusiast

One to Many

Hey.

I am doing a proof of concept and have come unstuck, my design is as below and I believe this design could work but am not sure how. However I admit my design may be incorrect for the data structures I am trying to work with and I am open to any suggestions.
Please advise.

------------------------------------------------------------------------------
1. I receive a 'LISTS' file which I process to staging.
------------------------------------------------------------------------------
-- A. Create POC Extract Table
CREATE MULTISET TABLE MMG_Get_BCP_H_YLISTS (
POLICY_CODE char(20),
TRANS_TYPE CHAR(2),
TRANS_SEQ_NO INTEGER,
LISTS_NAME CHAR(3),
UNIT_ID SMALLINT,
LISTS_COMBO_STRING CHAR(12));

-- B. Insert POC rows into Extract Table.
INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 1, 'A', 0, 'abc123def456');
INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 1, 'B', 1, '---abc------');
INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'A', 0, 'ghi789jkl012');
INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'A', 1, 'mno345pqr678');
INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'B', 1, '---def------');
INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'C', 1, '---xx-------');
INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'C', 1, '---xx------');
INSERT INTO MMG_Get_BCP_H_YLISTS VALUES('XYZ', 'DQ', 2, 'C', 1, '---xx------');

select * from MMG_Get_BCP_H_YLISTS order by 1
----------------------------end of staging-------------------------------------

------------------------------------------------------------------------------
2. I build a dimension as follows:
------------------------------------------------------------------------------
CREATE MULTISET TABLE MMG_Lst_lu_Lists (
Lst_Dim_Lists_Key_Id INTEGER,
Lst_Lists_Name CHAR(3),
Lst_Lists_Att_Id CHAR(12));

INSERT INTO MMG_Lst_lu_Lists(Lst_Lists_Name, Lst_Lists_Att_Id)
SELECT D1.List_Name_Rank,
bcp.LISTS_NAME as List_Name,
CASE WHEN bcp.LISTS_NAME = 'A' THEN SUBSTR(LISTS_COMBO_STRING,1,12)
WHEN bcp.LISTS_NAME = 'B' THEN SUBSTR(LISTS_COMBO_STRING,4,3)
WHEN bcp.LISTS_NAME = 'C' THEN SUBSTR(LISTS_COMBO_STRING,4,2)
ELSE 'Not Defined'
END
FROM MMG_Get_BCP_H_YLISTS bcp
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY D2.LISTS_NAME) As List_Name_Rank,
D2.LISTS_NAME
FROM (SELECT LISTS_NAME
FROM MMG_Get_BCP_H_YLISTS
GROUP BY 1) D2) D1
ON bcp.Lists_Name = D1.Lists_Name
ORDER BY 1,2,3;

select * from MMG_Lst_lu_Lists order by 1
-------------------------end of dimension-------------------------------------

------------------------------------------------------------------------------
3. I populate my fact as follows:
------------------------------------------------------------------------------
Insert into FACTtbl
SELECT c.policy_code, c.policy_rsk_no, c.product_class, c.effective_dt, c.trans_seq, m.Lst_Dim_Lists_Key_Id
FROM cover_class_staging_tbl1 c
LEFT JOIN cover_class_staging_tbl2 q
ON c.POLICY_CODE = q.POLICY_CODE
AND c.POLICY_RSK_NO = q.POLICY_RSK_NO
AND c.PRODUCT_CLASS = q.PRODUCT_CLASS
AND c.EFFECTIVE_DT = q.EFFECTIVE_DT
AND c.TRANS_SEQ = q.TRANS_SEQ_NO
LEFT JOIN MMG_Lst_lu_Lists m
ON substring(c.Lists_Name,1,1) = m.Lst_Lists_Name
OR substring(c.Lists_Name,2,2) = m.Lst_Lists_Name
--------------------------------end of fact------------------------------------

/* DDL for cover_class_staging_tbl1 & 2
CREATE SET TABLE cover_class_staging_tbl1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
POLICY_CODE CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
POLICY_RSK_NO INTEGER,
PRODUCT_CLASS CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
EFFECTIVE_DT CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
TRANS_SEQ INTEGER,
LISTS_NAME CHAR(3)) -- will have the list name of each list per transaction eg 'ABC'
PRIMARY INDEX ( POLICY_CODE );

INSERT INTO cover_class_staging_tbl1 VALUES('GEI/QMV/01879674', 1, 'QMV', '20090813', 1, 'AB');

CREATE MULTISET TABLE cover_class_staging_tbl2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
POLICY_CODE CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
TRANS_SEQ_NO INTEGER,
POLICY_RSK_NO SMALLINT,
PRODUCT_CLASS CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
EFFECTIVE_DT CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( POLICY_CODE ,TRANS_SEQ_NO );

*/ -- end DDL

I need one row coming back which can link to the dimension and show the multiple lists, not sure how - please help!
1 REPLY
Enthusiast

Re: One to Many

Will this do?
substring(c.Lists_Name,1,1) = m.Lst_Lists_Name
Union
Substring(c.Lists_Name,2,2) = m.Lst_Lists_Name

SELECT c.policy_code, c.policy_rsk_no, c.product_class, c.effective_dt, c.trans_seq, m.Lst_Dim_Lists_Key_Id
FROM cover_class_staging_tbl1 c
LEFT JOIN cover_class_staging_tbl2 q
ON c.POLICY_CODE = q.POLICY_CODE
AND c.POLICY_RSK_NO = q.POLICY_RSK_NO
AND c.PRODUCT_CLASS = q.PRODUCT_CLASS
AND c.EFFECTIVE_DT = q.EFFECTIVE_DT
AND c.TRANS_SEQ = q.TRANS_SEQ_NO
LEFT OUTER JOIN MMG_Lst_lu_Lists m
ON substring(c.Lists_Name,1,1) = m.Lst_Lists_Name

Union

SELECT c.policy_code, c.policy_rsk_no, c.product_class, c.effective_dt, c.trans_seq, m.Lst_Dim_Lists_Key_Id
FROM cover_class_staging_tbl1 c
LEFT JOIN cover_class_staging_tbl2 q
ON c.POLICY_CODE = q.POLICY_CODE
AND c.POLICY_RSK_NO = q.POLICY_RSK_NO
AND c.PRODUCT_CLASS = q.PRODUCT_CLASS
AND c.EFFECTIVE_DT = q.EFFECTIVE_DT
AND c.TRANS_SEQ = q.TRANS_SEQ_NO
LEFT OUTER JOIN MMG_Lst_lu_Lists m
Substring(c.Lists_Name,2,2) = m.Lst_Lists_Name