Regd composite key creation on derived fields

[Anonymous]

Hi All,

I have the fields like this,

Table1 :

Load

  Col1 & '_' & Col2  AS Key1,

  Col1 & '_' & Col2 & '_' Col3 AS Key2

  Col4

From

[a.qvd] (qvd);

Table2 :

Load

  Col1 & '_' & Col2  AS Key1,

Col1 & '_' & Col2 & '_' Col3 AS Key2

Col5

From

[b.qvd] (qvd);

I want to create a composite key on Key1 and Key2. I mean as below,

     Key1 & '|' & '|' Key2 as Comp_Key1Key2

Note :

  The purpose is to eliminate the synthetic key and also to do minimal script changes so that it doesn't impact the application on a whole.

[aarkay29]

Why do you need the Key 1 when the same col1 and col2 exist in key 3

What i mean is

When you concate KEY 1 and KEY2

KEY 1 is same as first part of KEY 2 , it is the same combination instead of being unique .

I believe you can simply use KEY 2 instead of combining both the keys.

But if you still want it you can do like this

Table1 :

Load *,

Key1 & '|' & '|' Key2 as Comp_Key1Key2;

Load

  Col1 & '_' & Col2  AS Key1,

  Col1 & '_' & Col2 & '_' Col3 AS Key2

  Col4

From

[a.qvd] (qvd);

Table2 :

Load *,

Key1 & '|' & '|' Key2 as Comp_Key1Key2;

Load

  Col1 & '_' & Col2  AS Key1,

Col1 & '_' & Col2 & '_' Col3 AS Key2

Col5

From

[b.qvd] (qvd);