Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I meet an issue with the inner and outer join.
Indeed, I find the same result ans I don't understand why?
Below my script:
IN_TAB:
SQL SELECT
Transaction_Type,
Transaction_Version,
Company,
Unit,
Date
FROM MYODBC
WHERE Date>= '20190101';
INNER JOIN
SELECT
Fiscal_Year,
Journal_Description,
Reference_Field1,
Reference_Field2,
FII9CD AS Reference_Field4
FROM MYODBC
WHERE Date>= '20190101';
OUT_TAB:
SQL SELECT
SQL SELECT
Transaction_Type,
Transaction_Version,
Company,
Unit,
Date
FROM MYODBC
WHERE Date>= '20190101';
OUTER JOIN
SELECT
Fiscal_Year,
Journal_Description,
Reference_Field1,
Reference_Field2,
FII9CD AS Reference_Field4
FROM MYODBC
WHERE Date>= '20190101';
Please could you help me to identify what is wrong in my script.
Thank you for your help.
Hi,
You do not have any primary key field between these two tables that's why it is throwing wrong output.
You should have a common field to connect two tables.
IN_TAB:
SQL SELECT
Transaction_Type,
Transaction_Version,
Company,
Unit,
Date
FROM MYODBC
WHERE Date>= '20190101';
INNER JOIN
SELECT
Fiscal_Year,
Journal_Description,
Reference_Field1,
Reference_Field2,
FII9CD AS Reference_Field4
FROM MYODBC
WHERE Date>= '20190101';
Regards,
Sultan
Hi @sultanam ,
Thank you for your answer.
I have the field Company as key in the two tables (and also as Key I have the field Date).
I add the field Company in my second table and I run again my script. But the result is still not correct.
Do you think that my problem is due to the type of my data source because I use an ODBC connection ?
Thank you in advance for your help.
Hi,
I am not able to see you mentioned Company field present in your second table ( After inner join).
Could you please write you two tables here with inner join only.
Hello @sultanam ,
Thank you very much for your help.
Below, I wrote my script with the inner join:
IN_TAB:
SQL SELECT
Transaction_Type,
Transaction_Version,
Company,
Unit,
Date
FROM MYODBC
WHERE Date>= '20190101';
INNER JOIN
SELECT
Fiscal_Year,
Journal_Description,
Company,
Date,
Reference_Field1,
Reference_Field2,
Reference_Field4
FROM MYODBC
WHERE Date>= '20190101';
Event, if I have clearly a common key between my two tables, my inner join does not work. I can confirm it when I try the script with the outer join. I find the same result as the script with the inner join.
Please could you help me to resolve this problem.
Thank you in advance for your help.
Hi,
Could you please attach sample data, it will be easy for me to answer.