Automate

​​Hi Experts,

I need some help regarding query scripts.
I am trying to build a script which download multiple revisions of material master.
For that I am using table MARA, AEOI, AENR
Table MARA is used for selection for material, AEOI is used to fetch revision(it can store multiple revisions of materials) and then table AENR is used to fetch the change number details.

Now for a scenario,where a revision is assigned to a material in SAP my query works , that is it fetches details from all 3 tables.
But cases where the material is newly/just created with no revision assigned, I still want to see that material as output with field selected from table AEOI & AENR as empty .
How can I achieve this .? I have tried setting up my join between MARA & AEOI as left outer join but that doesn't give desired results.

I am attaching my query here for reference.

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH |
------------------------------

Hi Aakansha

Your query worked for me with one tweak.  You had multiple options for material number, and the first one with the IN operator used an AND at the end.  That meant you had to also have a match with the LIKE operator



Hope that helps.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 05-18-2021 06:34
From: Aakansha Gupta
Subject: Query for material Revison

​​Hi Experts,

I need some help regarding query scripts.
I am trying to build a script which download multiple revisions of material master.
For that I am using table MARA, AEOI, AENR
Table MARA is used for selection for material, AEOI is used to fetch revision(it can store multiple revisions of materials) and then table AENR is used to fetch the change number details.

Now for a scenario,where a revision is assigned to a material in SAP my query works , that is it fetches details from all 3 tables.
But cases where the material is newly/just created with no revision assigned, I still want to see that material as output with field selected from table AEOI & AENR as empty .
How can I achieve this .? I have tried setting up my join between MARA & AEOI as left outer join but that doesn't give desired results.

I am attaching my query here for reference.

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH |
------------------------------

Hi Sigrid,

Thanks for spotting this . I have fixed this one on my side.
But the issue still remains same.
I am looking at a scenario where material only exist in MARA and not yet in table AEOI.
In your example, revision (01) is assigned to both materials and thus they are outputted.
In my example, the material is created (exists in MARA) but revision is not assigned yet, that means no entry in table AEOI and thus none in AENR also.
As you might have noticed, i have left outer join between AEOI & MARA but that is also not helping :-(
​

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074
------------------------------

Original Message:
Sent: 05-19-2021 09:49
From: Sigrid Kok
Subject: Query for material Revison

Hi Aakansha

Your query worked for me with one tweak.  You had multiple options for material number, and the first one with the IN operator used an AND at the end.  That meant you had to also have a match with the LIKE operator



Hope that helps.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-18-2021 06:34
From: Aakansha Gupta
Subject: Query for material Revison

​​Hi Experts,

I need some help regarding query scripts.
I am trying to build a script which download multiple revisions of material master.
For that I am using table MARA, AEOI, AENR
Table MARA is used for selection for material, AEOI is used to fetch revision(it can store multiple revisions of materials) and then table AENR is used to fetch the change number details.

Now for a scenario,where a revision is assigned to a material in SAP my query works , that is it fetches details from all 3 tables.
But cases where the material is newly/just created with no revision assigned, I still want to see that material as output with field selected from table AEOI & AENR as empty .
How can I achieve this .? I have tried setting up my join between MARA & AEOI as left outer join but that doesn't give desired results.

I am attaching my query here for reference.

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH |
------------------------------

Hi again Aakansha,

It's the extra table on the left hand side of the outer join - AENR, as it has an inner join with AEOI.  Because of this, you will only get materials that have change numbers assigned.

If you remove AENR, you will get what I believe you're expecting - materials with and without change numbers assigned.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 05-20-2021 01:40
From: Aakansha Gupta
Subject: Query for material Revison

Hi Sigrid,

Thanks for spotting this . I have fixed this one on my side.
But the issue still remains same.
I am looking at a scenario where material only exist in MARA and not yet in table AEOI.
In your example, revision (01) is assigned to both materials and thus they are outputted.
In my example, the material is created (exists in MARA) but revision is not assigned yet, that means no entry in table AEOI and thus none in AENR also.
As you might have noticed, i have left outer join between AEOI & MARA but that is also not helping :-(
​

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074

Original Message:
Sent: 05-19-2021 09:49
From: Sigrid Kok
Subject: Query for material Revison

Hi Aakansha

Your query worked for me with one tweak.  You had multiple options for material number, and the first one with the IN operator used an AND at the end.  That meant you had to also have a match with the LIKE operator



Hope that helps.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-18-2021 06:34
From: Aakansha Gupta
Subject: Query for material Revison

​​Hi Experts,

I need some help regarding query scripts.
I am trying to build a script which download multiple revisions of material master.
For that I am using table MARA, AEOI, AENR
Table MARA is used for selection for material, AEOI is used to fetch revision(it can store multiple revisions of materials) and then table AENR is used to fetch the change number details.

Now for a scenario,where a revision is assigned to a material in SAP my query works , that is it fetches details from all 3 tables.
But cases where the material is newly/just created with no revision assigned, I still want to see that material as output with field selected from table AEOI & AENR as empty .
How can I achieve this .? I have tried setting up my join between MARA & AEOI as left outer join but that doesn't give desired results.

I am attaching my query here for reference.

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH |
------------------------------

​HI Sigrid,

Thanks for this info. You are right, if I remove AENR, i get the desired data .

But is this the standard behavior that we are allowed to have only one left outer Join and no tables to the left of it .?is there any way to deal with it

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074
------------------------------

Original Message:
Sent: 05-20-2021 08:51
From: Sigrid Kok
Subject: Query for material Revison

Hi again Aakansha,

It's the extra table on the left hand side of the outer join - AENR, as it has an inner join with AEOI.  Because of this, you will only get materials that have change numbers assigned.

If you remove AENR, you will get what I believe you're expecting - materials with and without change numbers assigned.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-20-2021 01:40
From: Aakansha Gupta
Subject: Query for material Revison

Hi Sigrid,

Thanks for spotting this . I have fixed this one on my side.
But the issue still remains same.
I am looking at a scenario where material only exist in MARA and not yet in table AEOI.
In your example, revision (01) is assigned to both materials and thus they are outputted.
In my example, the material is created (exists in MARA) but revision is not assigned yet, that means no entry in table AEOI and thus none in AENR also.
As you might have noticed, i have left outer join between AEOI & MARA but that is also not helping :-(
​

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074

Original Message:
Sent: 05-19-2021 09:49
From: Sigrid Kok
Subject: Query for material Revison

Hi Aakansha

Your query worked for me with one tweak.  You had multiple options for material number, and the first one with the IN operator used an AND at the end.  That meant you had to also have a match with the LIKE operator



Hope that helps.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-18-2021 06:34
From: Aakansha Gupta
Subject: Query for material Revison

​​Hi Experts,

I need some help regarding query scripts.
I am trying to build a script which download multiple revisions of material master.
For that I am using table MARA, AEOI, AENR
Table MARA is used for selection for material, AEOI is used to fetch revision(it can store multiple revisions of materials) and then table AENR is used to fetch the change number details.

Now for a scenario,where a revision is assigned to a material in SAP my query works , that is it fetches details from all 3 tables.
But cases where the material is newly/just created with no revision assigned, I still want to see that material as output with field selected from table AEOI & AENR as empty .
How can I achieve this .? I have tried setting up my join between MARA & AEOI as left outer join but that doesn't give desired results.

I am attaching my query here for reference.

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH |
------------------------------

Hi Aakansha 

Query has limits, so yes, we are limited to 1 outer join.   It will never support every possibility that SQL will support.  It's geared more for business users.

Alternatives:

(1) Run your query without the 3rd table, then use a transaction script against the change master to download the description.

(2) We've had some customers create queries in SQVI and then shared them with groups.  Those can be leveraged in Query via choosing Infosets and searching for your query name.  

Hope that helps.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 05-21-2021 00:57
From: Aakansha Gupta
Subject: Query for material Revison

​HI Sigrid,

Thanks for this info. You are right, if I remove AENR, i get the desired data .

But is this the standard behavior that we are allowed to have only one left outer Join and no tables to the left of it .?is there any way to deal with it

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074

Original Message:
Sent: 05-20-2021 08:51
From: Sigrid Kok
Subject: Query for material Revison

Hi again Aakansha,

It's the extra table on the left hand side of the outer join - AENR, as it has an inner join with AEOI.  Because of this, you will only get materials that have change numbers assigned.

If you remove AENR, you will get what I believe you're expecting - materials with and without change numbers assigned.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-20-2021 01:40
From: Aakansha Gupta
Subject: Query for material Revison

Hi Sigrid,

Thanks for spotting this . I have fixed this one on my side.
But the issue still remains same.
I am looking at a scenario where material only exist in MARA and not yet in table AEOI.
In your example, revision (01) is assigned to both materials and thus they are outputted.
In my example, the material is created (exists in MARA) but revision is not assigned yet, that means no entry in table AEOI and thus none in AENR also.
As you might have noticed, i have left outer join between AEOI & MARA but that is also not helping :-(
​

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074

Original Message:
Sent: 05-19-2021 09:49
From: Sigrid Kok
Subject: Query for material Revison

Hi Aakansha

Your query worked for me with one tweak.  You had multiple options for material number, and the first one with the IN operator used an AND at the end.  That meant you had to also have a match with the LIKE operator



Hope that helps.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-18-2021 06:34
From: Aakansha Gupta
Subject: Query for material Revison

​​Hi Experts,

I need some help regarding query scripts.
I am trying to build a script which download multiple revisions of material master.
For that I am using table MARA, AEOI, AENR
Table MARA is used for selection for material, AEOI is used to fetch revision(it can store multiple revisions of materials) and then table AENR is used to fetch the change number details.

Now for a scenario,where a revision is assigned to a material in SAP my query works , that is it fetches details from all 3 tables.
But cases where the material is newly/just created with no revision assigned, I still want to see that material as output with field selected from table AEOI & AENR as empty .
How can I achieve this .? I have tried setting up my join between MARA & AEOI as left outer join but that doesn't give desired results.

I am attaching my query here for reference.

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH |
------------------------------

Thanks Sigrid.

The alternatives are really helpful . I will try these definitely .

Regards
Aakansha​

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074
------------------------------

Original Message:
Sent: 05-21-2021 08:57
From: Sigrid Kok
Subject: Query for material Revison

Hi Aakansha

Query has limits, so yes, we are limited to 1 outer join.   It will never support every possibility that SQL will support.  It's geared more for business users.

Alternatives:

(1) Run your query without the 3rd table, then use a transaction script against the change master to download the description.

(2) We've had some customers create queries in SQVI and then shared them with groups.  Those can be leveraged in Query via choosing Infosets and searching for your query name.

Hope that helps.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-21-2021 00:57
From: Aakansha Gupta
Subject: Query for material Revison

​HI Sigrid,

Thanks for this info. You are right, if I remove AENR, i get the desired data .

But is this the standard behavior that we are allowed to have only one left outer Join and no tables to the left of it .?is there any way to deal with it

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074

Original Message:
Sent: 05-20-2021 08:51
From: Sigrid Kok
Subject: Query for material Revison

Hi again Aakansha,

It's the extra table on the left hand side of the outer join - AENR, as it has an inner join with AEOI.  Because of this, you will only get materials that have change numbers assigned.

If you remove AENR, you will get what I believe you're expecting - materials with and without change numbers assigned.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-20-2021 01:40
From: Aakansha Gupta
Subject: Query for material Revison

Hi Sigrid,

Thanks for spotting this . I have fixed this one on my side.
But the issue still remains same.
I am looking at a scenario where material only exist in MARA and not yet in table AEOI.
In your example, revision (01) is assigned to both materials and thus they are outputted.
In my example, the material is created (exists in MARA) but revision is not assigned yet, that means no entry in table AEOI and thus none in AENR also.
As you might have noticed, i have left outer join between AEOI & MARA but that is also not helping :-(
​

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH | +918130780074

Original Message:
Sent: 05-19-2021 09:49
From: Sigrid Kok
Subject: Query for material Revison

Hi Aakansha

Your query worked for me with one tweak.  You had multiple options for material number, and the first one with the IN operator used an AND at the end.  That meant you had to also have a match with the LIKE operator



Hope that helps.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA

Original Message:
Sent: 05-18-2021 06:34
From: Aakansha Gupta
Subject: Query for material Revison

​​Hi Experts,

I need some help regarding query scripts.
I am trying to build a script which download multiple revisions of material master.
For that I am using table MARA, AEOI, AENR
Table MARA is used for selection for material, AEOI is used to fetch revision(it can store multiple revisions of materials) and then table AENR is used to fetch the change number details.

Now for a scenario,where a revision is assigned to a material in SAP my query works , that is it fetches details from all 3 tables.
But cases where the material is newly/just created with no revision assigned, I still want to see that material as output with field selected from table AEOI & AENR as empty .
How can I achieve this .? I have tried setting up my join between MARA & AEOI as left outer join but that doesn't give desired results.

I am attaching my query here for reference.

Regards
Aakansha

------------------------------
Aakansha Gupta | SAP DATA MIGRATION CONSULTANT
Moog GmbH |
------------------------------