Find and Reserve: How to join columns of 2 tables by concatenating with leading zeroes?

[Prasad Raghu]

Hi,

I have 2 tables from 2 different schemas for which I need to create a relationship for creating a Find and Reserve tile. The challenge I have is that the Column A of Table A is related to Column B of Table B (both string datatype) with leading zeros i.e. Column A = Concat('0000',Column B). When I try to add relationship using Data Model, it only allows to map Column A and Column B without providing a way to add leading zeroes. I even tried to add relationship using datamaker and even there its the same case.

Could anyone guide me on how to join these columns by adding leading zeroes? Appreciate your support!

Thanks,
Prasad

------------------------------
Thanks,
Prasad
------------------------------

[Gene Howard]

I can not think of a way natively to create a relationship between two fields that have different values.

Would it be possible in the database that has the table that needs the extra Zeros to create a view and add a column to it that has the zeros added and then use the view?
To user views you would need to be using a newer version of 4.9.1 Portal.

------------------------------
Gene Howard
Principal Support Engineer
Broadcom
------------------------------

[Prasad Raghu]

Hi Gene,

Thanks for your quick response. We dont have the ability to alter the table to add a column to append zeroes. Yes I am using the latest version 4.9.1 portal. So you mean to say there is no other workaround within CA TDM portal or datamaker with which we can join these two columns by concatenating with leading zeroes?

Thanks,
Prasad

------------------------------
Thanks,
Prasad
------------------------------

[Gene Howard]

Not that I am aware of. You are trying to build a relationship in a database between two fields that do not have the same values.

I was not suggesting you alter the table but rather create a Database View where you create a virtual column with the new formatted data you need.

------------------------------
Gene Howard
Principal Support Engineer
Broadcom
------------------------------

[Prasad Raghu]

Hi Gene,

By default we are given the least amount of privileges so we dont have the access to create views by ourselves. So i wanted to try a solution which could be quickly built without external team dependency. Let me check with the application team if they could create a view for us. Thank you for sharing your inputs.

------------------------------
Thanks,
Prasad
------------------------------