DSXchange

Hi,

I am not able to use the join stage properly and getting wrong result.

I have two tables : Table A and Table B

Table A has following columns:
Country varchar (50), Country_code varchar(10)

Table B has :
Country_code varchar(10), Car_name varchar(10)

Table A records:
Country_code Country
------------------------------------
IN Pakistan

Table B records:
Country_code Car_name
-------------------------------------
IN Verna
IN BMW
PK Audi

When I try to do inner join output returns as 0 records (key column is country_code).
But when I use integer in place of varchar as data type of contry code, job works fine.


Please provide the solution that where I am committing the mistake.

Thanks,

Often this type of error is because one or another of the Country_Code columns has spaces in it. You can test this by doing a TRIM() of both columns in a stage before the join.

sanygup wrote: When I try to do inner join output returns as 0 records (key column is country_code).
But when I use integer in place of varchar as data type of contry code, job works fine.

This line just caught my eyes, If you change the datatype to integer and all the columns you mentioned is varchar, yes, you will be able to get the match as everything becomes zero but it will cause cartesian product and will not output correct result. Are you getting correct result even if the job is working fine.

Make sure you get the partitioning correct and check what Arnd suggested.

Somewhat off topic (more a data quality issue), why does IN decode to "Pakistan"?

Thanks you all (kunal,ray and arndw) for replying the topic, I got the issue resolved by applying link sort on input link of Join stage over 'country_code' which is a key field of Join stage. Now the result is as expected.