Skip to main content
To homepage To homepage
Solved

Using Collate on Join conditions

  • September 5, 2019
  • 4 replies
  • 101 views
S
Starter

I am joining 2 tables T1 and T2 based on a condition T1.A = T2.B

T1.A have values like 'wNFq' and T2.B have values like 'WNFQ', 'wnfQ', 'WnFq', 'wNFq'

For a regular join a particular row of 'wNFq' from T1 gets joined with all values: 'WNFQ', 'wnfQ','WnFq' and 'wNFq' of T2

My intention is to join only 'wNFq' from T1 to 'wNFq'.

I achieved the same using custom SQL insert and using the below  COLLATE function to join based on case sensitive sequencing using:

T1.A COLLATE Latin1_General_CS_AS_KS_WS = T2.B COLLATE Latin_1_General_CS_AS_KS_WS

Wandering if there is a different way to achieve this avoiding custom SQL.

Appreciate any help on the same.

 

Best answer by JTreadwell

Hi Supriyo, Thank you for posting this question. I have added the photo you sent to me into your original post. 

To handle this in Discovery Hub you don't you need to apply a transformation to the join column to make all characters uppercase or lowercase. Using this method you don't need to adjust the collation.

  1. Right click on T1.A > Clone Field
  2. Name A_UpperCase > Transform Values 
  3. Right click on T1.A_UpperCase > Field Transformation
  4. In the bottom of the Transformation pane on the right, change the operator to "To upper" > Click Add
  5. Perform steps 1-4 on T2.B
  6. Now create your table relation on T1.A_UpperCase = T2.B_UpperCase. 

The resulting solution should look like this:

This should solve your problem. 

Let me know if you need clarification. 

    4 replies

    JTreadwell
    Employee
    Forum|alt.badge.img+5
    • JTreadwell
    • Employee
    • 182 replies
    • Answer
    • September 5, 2019

    Hi Supriyo, Thank you for posting this question. I have added the photo you sent to me into your original post. 

    To handle this in Discovery Hub you don't you need to apply a transformation to the join column to make all characters uppercase or lowercase. Using this method you don't need to adjust the collation.

    1. Right click on T1.A > Clone Field
    2. Name A_UpperCase > Transform Values 
    3. Right click on T1.A_UpperCase > Field Transformation
    4. In the bottom of the Transformation pane on the right, change the operator to "To upper" > Click Add
    5. Perform steps 1-4 on T2.B
    6. Now create your table relation on T1.A_UpperCase = T2.B_UpperCase. 

    The resulting solution should look like this:

    This should solve your problem. 

    Let me know if you need clarification. 

    -Joseph Treadwell
    S
    Starter
    • sbanerjee1
    • Author
    • Starter
    • 2 replies
    • September 5, 2019

    Hi Joseph,

    Thanks for the response. I don't want to convert all values to upper case , then there will be no difference between 'wNFq' and 'WNFQ'.

    I want to implement the join based on the exact upper and lower case values with correct sequencing in upper and lower cases.

    So 'wNFq' should only join to 'wNFq' and not to 'WNFQ' or to 'WnfQ'

    Appreciate your help.

    JTreadwell
    Employee
    Forum|alt.badge.img+5
    • JTreadwell
    • Employee
    • 182 replies
    • September 5, 2019

    I see, Sorry I misunderstood. 

    In this case the only way to do this would be to change the collation on the whole database. OR use a custom table insert to write the custom script. 

    I do see how this would be a helpful feature and highly suggest you add this to Ideas section. 

    -Joseph Treadwell
    A
    • Anonymous
    • 0 replies
    • September 6, 2019

    Hi Supriyo

    I suggest hashing the join fields. With hashing you ensure that there is a difference between the to values WNFQ and WnfQ.

    And if you are using more keys in you lookup you could even experience a performance improvement in the lookup.

    Regards,
    Jan


    Terms and ConditionsCookie settingsAccessibility statement