What join columns should I take for ACDOCA and JVSO1 SAP tables?

Question

I have fetched two SAP ERP tables into Azure Synapse: ACDOCA and JVSO1.

Now I need to join these two tables. And the column EBELN is required to be in join condition, also both tables have around 40% of EBELN empty. Because of these empty values, these two tables produce a lot of data (In Billions).

What I have tried: I have picked one more column EBELP and joined both tables based on these two columns:

WHERE ACDOCA.EBELN = JVSO1.EBELN AND ACDOCA.EBELP = JVSO1.EBELP

But even after this condition, I am getting a lot of data.

What I want:

I want to join these two tables and have less amount of data (Not in Billions). Can you please suggest me more columns in both tables so that I can join both of the tables correctly with lesser amount of data.

Thanks

which data actully you want common dat from both table or common data from 1 table and all data from another table? — Pratik Lad, Jan 24 '23 at 06:49
if 40% of EBELN empty, then how adding EBELP column as joining condition will help ? Also you are joining with `and` operator between conditions. How this will help with empty EBELN data? — Aswin, Jan 24 '23 at 07:22
columns based on which you are joining the table if it has empty or null values the join condition will skip those rows. — Pratik Lad, Jan 24 '23 at 08:37

Aswin · Answer 1 · 2023-01-31T13:56:13.337

Joining conditions on acdoca.ebeln=JVSO1.ebeln and acdoca.ebelp=JVSO1.ebelp and on acdoca.ebeln=JVSO1.ebeln will give same result for rows with null values. I repro'd this with sample data.

Input data with null values:

ebeln	EBELP
null	A
AA	AA
null	B

enter image description here

Joining tables based on ebeln fields:

select * from acdoca full outer join JVSO1 on 
acdoca.ebeln=JVSO1.ebeln

enter image description here

Joining tables based on ebeln and ebelp fields:

select * from acdoca full outer join JVSO1 on 
acdoca.ebeln=JVSO1.ebeln and acdoca.ebelp=JVSO1.ebelp

enter image description here

Query should be written in such a way whenever ebeln field is null, condition should be on matching ebelp fields JOIN of tables should happen.

select * from acdoca full outer join JVSO1 
on
(acdoca.ebeln is not null and acdoca.ebeln=JVSO1.ebeln) 
or 
(acdoca.ebeln is null and acdoca.ebelp=JVSO1.ebelp)

or

select * from acdoca full outer join JVSO1 on 
(isnull(acdoca.ebeln,acdoca.ebelp) =
isnull(JVSO1.ebeln,JVSO1.ebelp))

Even when you add other matching columns, joining conditions should be like above queries.

What join columns should I take for ACDOCA and JVSO1 SAP tables?

1 Answers1