All Forums UDA
marcmc 112 posts Joined 12/05
29 Jan 2010
'Pre-Built' dimension

I am trying to understand the concept of a pre-built dimension.The following DDL exemplifies my problem:-- Step 1 - Create the staging table.CREATE SET TABLE marc_centlook ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(Ge_lookup_id CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_source_id CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_lookup_code CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_lookup_desc CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_lookup_parent CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,Ge_lookup_key CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( Ge_lookup_id ,Ge_lookup_code );-- Step 2. Populate the staging tableINSERT INTO marc_centlook VALUES('RI_1', 'H', 'A1', 'EOL', '', '');INSERT INTO marc_centlook VALUES('RI_1', 'H', 'A2', 'Scrapped', '', '');-- Step 3. Create the the staging tableCREATE SET TABLE vht_su_risk_intelligence ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(Vht_risk_intelligence_id SMALLINT,Vht_risk_intelligence_code CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,Vht_risk_intelligence_desc CHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( Vht_risk_intelligence_code );-- Step 4. Populate the the staging tableINSERT INTO vht_su_risk_intelligence SELECT ROW_NUMBER() OVER (ORDER BY Ge_lookup_code) + 0,Ge_lookup_code,Ge_lookup_descFROM marc_centlook BCPWHERE NOT EXISTS(SELECT *FROM vht_su_risk_intelligence SURRWHERE SURR.Vht_risk_intelligence_code = BCP.Ge_lookup_codeAND Ge_lookup_id = 'RI_1')AND Ge_lookup_id = 'RI_1'------------------------------------------------ ----------------------------This gives me:1 A1 EOL2 A2 ScrappedI am looking to create:1 A1 EOL2 A2 Scrapped3 A1/A2 EOL/Scrapped 4 A1/A2 Scrapped/EOLOr...1 A1 EOL2 A1 EOL3 A2 Scrapped4 A2 Scrapped5 A1/A2 EOL 6 A1/A2 ScrappedThis needs to work with more than two values for example another row for above could be A3 NCBAny ideas the quickest and most elegant way to do this.

You must sign in to leave a comment.