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~~~
Concatenation of Columns From Different Tables in Database
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Code: Select all
SELECT ProductType.CODE AS ProductTypeCode From ProductType;
SELECT ProductName.CODE As ProductNameCode From ProductName;
SELECT FundType.CODE As FundTypeCode From FundType;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn