Since you have a fixed maximum of five licenses per customer it's probably easiest to use five load statements:
LIC_NUMBER_1 as LIC_NUMBER,
LIC_TYPE_1 as LIC_TYPE,
LIC_STATUS_CODE_1 as LIC_STATUS_CODE,
LIC_NAME_1 as LIC_NAME,
LIC_EFF_DATE_1 as LIC_EFF_DATE,
LIC_END_DATE_1 as LIC_END_DATE
WHERE Len(Trim(LIC_NUMBER_1)) >0
LIC_NUMBER_2 as LIC_NUMBER,
LIC_TYPE_2 as LIC_TYPE,
LIC_STATUS_CODE_2 as LIC_STATUS_CODE,
LIC_NAME_2 as LIC_NAME,
LIC_EFF_DATE_2 as LIC_EFF_DATE,
LIC_END_DATE_2 as LIC_END_DATE
WHERE Len(Trim(LIC_NUMBER_2)) >0
LIC_NUMBER_3 as LIC_NUMBER,
Thanks for the reply. A couple of things that I didn't mention previously are:
- There are about 500,000 records in the database table that I'm pulling from.
- For each of those 500,000 records, in additional to the other unique/distinct fields, there are nine separate field groupings per record, each containing from zero to five data values. The license information mentioned in my first post is one of the nine groupings associated with each record.
With this additional information would there be a more efficient way to load the information rather than repeatedly reading through the source data multiple times?