Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Ive run into a situation where I am trying to run an inner join in a tMap but its not joining the data as expected. I can join the data in a query directly and have the expected values returned. I would simply use the join as my input data, however I also need to handle the rejects, and the handling in the tMap makes sense if it would only work correctly in this instance.
I have many parts of my job where this behavior works as expected so im at a loss for why its not working in this instance. I have attached some screen shots of the data going into the tMap (for this instance I added where clauses to my queries to only pull 1 row each where the data would match so that you can see a row which should match in the traces).
The fields that are being joined are both fields being handled as strings at first I did not have the lengths specified from one of my feeds, thinking that may be a problem I added the lengths onto those fields which didnt change the outcome at all.
The query below is what I am trying to do in the tMap, once again I have added limited my results to a single result for illustration purposes. The below query returns the 1 expected row.
Ive extremely oversimplified my results here to make it easier to explain, essentially in my real job I need to handle the rejected data differently than the joined data, and I need some of the fields from the joined table to handle a joined row properly. I am working on scoring the quality of data and need this join to determine if the phone number corresponds to the country we have listed for the property and then we need the other data from the lookup table to determine if the phone number is a valid number for the country dial code that is specified.
Ill work on using trims and uppers in my queries to see if that will change anything, which looking at the data in the traces and the fact that the join works at the database level doesnt really give me a good feeling on getting it to work that way. Has anyone experienced this kind of issue with joins before? Ive used many joins throughout this particular job without any issues, so I just don't understand why this one is causing such a headache for me.
I have many parts of my job where this behavior works as expected so im at a loss for why its not working in this instance. I have attached some screen shots of the data going into the tMap (for this instance I added where clauses to my queries to only pull 1 row each where the data would match so that you can see a row which should match in the traces).
The fields that are being joined are both fields being handled as strings at first I did not have the lengths specified from one of my feeds, thinking that may be a problem I added the lengths onto those fields which didnt change the outcome at all.
The query below is what I am trying to do in the tMap, once again I have added limited my results to a single result for illustration purposes. The below query returns the 1 expected row.
select h.phone, h.iso_country_code, h.phone_country_code, d.iso_country_code, d.country_dial_code from trx_hotel as h inner join lkp_country_dial_codes as d on h.phone_country_code=d.country_dial_code and h.iso_country_code=d.iso_country_code where h.phone = 'xxxxxxxxxx';
Ive extremely oversimplified my results here to make it easier to explain, essentially in my real job I need to handle the rejected data differently than the joined data, and I need some of the fields from the joined table to handle a joined row properly. I am working on scoring the quality of data and need this join to determine if the phone number corresponds to the country we have listed for the property and then we need the other data from the lookup table to determine if the phone number is a valid number for the country dial code that is specified.
Ill work on using trims and uppers in my queries to see if that will change anything, which looking at the data in the traces and the fact that the join works at the database level doesnt really give me a good feeling on getting it to work that way. Has anyone experienced this kind of issue with joins before? Ive used many joins throughout this particular job without any issues, so I just don't understand why this one is causing such a headache for me.
