All Forums UDA
marcmc 112 posts Joined 12/05
14 Aug 2009
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 TableCREATE 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' ENDFROM MMG_Get_BCP_H_YLISTS bcpJOIN (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) D1ON bcp.Lists_Name = D1.Lists_NameORDER 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 FACTtblSELECT c.policy_code, c.policy_rsk_no, c.product_class, c.effective_dt, c.trans_seq, m.Lst_Dim_Lists_Key_IdFROM cover_class_staging_tbl1 cLEFT 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_NOLEFT JOIN MMG_Lst_lu_Lists mON substring(c.Lists_Name,1,1) = m.Lst_Lists_NameOR 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 DDLI need one row coming back which can link to the dimension and show the multiple lists, not sure how - please help!

Bhanu 5 posts Joined 04/09
14 Aug 2009

Will this do? substring(c.Lists_Name,1,1) = m.Lst_Lists_Name Union Substring(c.Lists_Name,2,2) = m.Lst_Lists_NameSELECT c.policy_code, c.policy_rsk_no, c.product_class, c.effective_dt, c.trans_seq, m.Lst_Dim_Lists_Key_IdFROM cover_class_staging_tbl1 cLEFT 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_NOLEFT OUTER JOIN MMG_Lst_lu_Lists mON substring(c.Lists_Name,1,1) = m.Lst_Lists_NameUnionSELECT c.policy_code, c.policy_rsk_no, c.product_class, c.effective_dt, c.trans_seq, m.Lst_Dim_Lists_Key_IdFROM cover_class_staging_tbl1 cLEFT 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_NOLEFT OUTER JOIN MMG_Lst_lu_Lists mSubstring(c.Lists_Name,2,2) = m.Lst_Lists_Name

You must sign in to leave a comment.