0

This is what I am currently joining:

INNER JOIN us_raw.l_dmw_order_report t1 on t2.channel_uid = t1.customer_id

However, I need it so that it's like this instead:

INNER JOIN us_raw.l_dmw_order_report t1 on t2.channel_uid = ('UNU' || t1.customer_id)

When I do that, it does not seem to pull what I need, so I'm wondering if there is another way to do what I'm trying to do. Basically, t1's customer_id is t2's channel_uid, except with a 'UNU' in front of it (yea, idk who created this table but apparently that's the only difference, and I spot-checked to confirm).

Please let me know if you need more information, working with a confidential DB.

Thanks,

Z41N
  • 97
  • 10
  • Can you share samples of the `t2.channel_uid` and `t1.customer_id`? – urdearboy Mar 02 '20 at 17:51
  • @urdearboy hi, here is an example: channel_uid sample is UNUUS621111111463170 customer_id US621111111463170, and this is the case for 80% of the data, so the only difference is the UNU in the beginning – Z41N Mar 02 '20 at 18:09
  • So you want to create `t2.channel_uid` by concatenating the `UNU` prefix and value of the column `t1.customer_id`, am I right? I would try `...ON t2.channel_uid = CONCAT('UNU', t1.customer_id)`. – David Ferenczy Rogožan Mar 02 '20 at 18:16
  • 1
    Also, please update your question with expected and actual behavior. What does "_does not seem to pull what I need_" exactly mean, what results are you getting and how is it different from the expected result? – David Ferenczy Rogožan Mar 02 '20 at 18:30

2 Answers2

1

If I understand it right, you want to construct the value of t2.channel_uid by concatenating the prefix UNU and the value of t1.customer_id column. If that's the case, the following should work:

INNER JOIN us_raw.l_dmw_order_report t1 on t2.channel_uid = CONCAT('UNU', t1.customer_id)

Depending on what data types of your columns, you may need some type casting.

David Ferenczy Rogožan
  • 23,966
  • 9
  • 79
  • 68
0

I was able to find the answer by forcing the join on a substring:

INNER JOIN l_act_account t2 ON customer_id = SUBSTRING(t2.channel_uid,4,18)

Instead of trying to add the characters, I just forced it to disregard in the table that had the characters lol

Z41N
  • 97
  • 10