UDA
Enthusiast

## '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 table
INSERT 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 table
CREATE 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 table
INSERT INTO vht_su_risk_intelligence
SELECT ROW_NUMBER() OVER (ORDER BY Ge_lookup_code) + 0,
Ge_lookup_code,
Ge_lookup_desc
FROM marc_centlook BCP
WHERE NOT EXISTS
(SELECT *
FROM vht_su_risk_intelligence SURR
WHERE SURR.Vht_risk_intelligence_code = BCP.Ge_lookup_code
AND Ge_lookup_id = 'RI_1'
)
AND Ge_lookup_id = 'RI_1'

----------------------------------------------------------------------------

This gives me:
1 A1 EOL
2 A2 Scrapped

I am looking to create:

1 A1 EOL
2 A2 Scrapped
3 A1/A2 EOL/Scrapped
4 A1/A2 Scrapped/EOL

Or...

1 A1 EOL
2 A1 EOL
3 A2 Scrapped
4 A2 Scrapped
5 A1/A2 EOL
6 A1/A2 Scrapped

This needs to work with more than two values for example another row for above could be A3 NCB
Any ideas the quickest and most elegant way to do this.