Concatenation of Columns From Different Tables in Database

[kate_gkl]

Hi all.

I am having problem in concatenating columns with the same field name from different tables in database.

Let me explain in more detail. I am having three tables: ProductType, ProductName and FundType. Each of these tables is having a field called CODE.

Now, I need to concatenate the CODE field data from these tables into only 1 field. For example,

ProductType.CODE = AB
ProductName.CODE = CD
FundType.CODE = EF

My output table CODE field should have ABCDEF as the data.

I understand that we can use a Transformer stage to concatenate string. But unfortunately, it cannot have multiple input links. It means I am not allowed to combine the tables and get the CODE field from each table.

May I know what is the way to solve this problem? Hope you guys can help me and provide some guidance. Thanks a lot.

Cheers~~~

[ray.wurlod]

Use alias names in the derivation

Code: Select all

SELECT ProductType.CODE AS ProductTypeCode From ProductType;
SELECT ProductName.CODE As ProductNameCode From ProductName;
SELECT FundType.CODE As FundTypeCode From FundType;

You can do these in the Derivations field in the Columns grid; then inspect the generated SQL. Set up the unabmbigous names in the Column name field, and the appropriate derivation in the Derivations field.

[vmcburney]

When you are joining multiple tables from the same database you have two options, join them in the select statement or join them via a transformer using reference lookups.

I assume there are key fields that link each table such as PRODUCT_ID. In you SQL stage you can use user defined SQL to join them:

SELECT ProductType.CODE AS ProductTypeCode,
ProductName.CODE As ProductNameCode ,
FundType.CODE As FundTypeCode
From ProductType, ProductName, FundType
WHERE ProductType.Product_Id = ProductName.Product_Id
AND ProductName.Product_Id = FundType.ProductId

This gives you three input fields that you can concatenate together in a transformer. Be sure to check for a NULL.

Using reference lookups you would read the largest table in as a input stream and put the other two code lists into hash files. The hash file would consist of the PRODUCT_ID field and the CODE field. You then lookup both hash files in a transformer and concatenate them into one field.