DSXchange

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~~~

Use alias names in the derivation 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.

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.