Union with Nulls

[Former Member]

Hi Gurus

I have done union on AUFK and RESB

Variable Selections on BUKRS from AUFK table.

When I am selecting the variable value I am getting single record instead of 2 records due to null values(?)

I want the below desired data.

[Former Member · Accepted solution]

Attached the correct example.

If I give BUKRS variable selection 6149, then my out put is only single record because of nulls. but i need all the 10 records.

** Variable selection is on BUKRS.

I have two tables AFKO, RESB and I have done Union.

For Example :

AFKO data (Header Level) ;  RESB - table at Detailed(Item Level)

When I am giving Variable selection BUKRS i am getting single record as next row output is with Null. If i go with RSNUM then I am getting 10 records.

I tried creating dummy BUKRS in RESB CV and tried to put it with UNION as it is dummy(no records) so i am not getting my desired data

Example :

[Former Member · Accepted solution]

Hi,

May I know why you want to create UNION between header and item tables. Ideally it should be inner join from header to item table with cardinality 1 to n.

Try this which gives you desired result.

Regards,

Venkat N.

[Former Member · Accepted solution]

It is as per the user Requirement

He want to see all the order related materials at header level,

At Item level he want to see all the values no. materials, etc...

[former_member226419 · Accepted solution]

Hi,

But as told above it will be done with the help of inner join.

I just tried on my system and got below output.

You dont want like below?

BR

Sumeet

[former_member185132 · Accepted solution]

As the other contributors have pointed out, you need to use Inner join if you want to get all 10 RESB records when filtering for a comp code value.

Doing a union will not work here - the RESB table doesn't have a BUKRS field, so even if you add it to a union this field will be null. Obviously when you filter for a condition of BUKRS=6149, it will not pick up the RESB records as they don't match the condition. All of this is exactly how a UNION is supposed to work.

[Former Member · Accepted solution]

BUKRS-AFKO	CHARG-AFKO	MATNR-AFKO	MATNR-RESB	CHARG-RESB
6149	2301	81200	?	?
?	?	?	ABC-100	23-100
?	?	?	ABC-200	23-101

Let me clarify once again.

There are total 3 Records because Union is on RSNUM and WERKS(I am getting above result),, 1 record from header i,e AFKO and 2 records from Item level(RESB) ......

Requirement is, At Header level single row and Item from 2nd row ONWARDS. I have given requirement.

Issue,

On BUKRS i have variable Selection...

BUKRS is from (AFKO table), I am getting only 1 record, because there is Null (?) value

Now If I do Join I will get only 2 records instead of 3 records,

My Requirement: I should get 3 records as shown above when I give the Variable Selection BUKRS = 6149

[Former Member · Accepted solution]

Yes sumett,

my requirement i posted below...

[Former Member · Accepted solution]

True... Suhas... That is the reason I posted here to get any alternate solution....

Hello Gurus...

How to over come in this scenario......

-Regards

[former_member185132 · Accepted solution]

Here's one possible solution:

Create a view (call it V1) that joins AFKO and RESB, using the RSNUM field. In this view pull all RESB fields and the AFKO-BUKRS  field. Do not pull any other fields from AFKO.

Then in another view (say V2), do a union between V1 and AFKO. That will meet the requirement.

If you are doing all this in a calc view, then V1 would be a join node in the calc view, and V2 would be a union node taking V1 and AFKO as inputs.

[Former Member · Accepted solution]

In fact I have done same thing.. till yesterday i didnt get the desired data.

But today I redeployed it with other user id's it started working...

Strange...

Any how it started working for me...

* To get BUKRS we have to join AUFK and  AFKO(see tables to understand easy)

In detail.

CV1 - >  Join1 on  AFKO With RESB (with RSNUM). here we get the BUKRS.

CV2 -> Now again take a join of AFKO and RESB then Union with CV1.

now if we give the Variable selection on company code with 6149 we We will get the desired data.

Regards

Lajwanth.

[Former Member · Accepted solution]

Thank you Suhas....