SQVI question

[Former Member]

Hi All,

     This is the scenario.

     Supposing i have 2 Sales Order. SO1 and SO2

     SO1 has no Delivery document yet and SO2 has already a Delivery Doc

     1st Query I linked table VBAK(VBELN) --> VBAP (VBELN)

           After execution both SO1 and SO2 appeared so no problem about that.

     2nd Query I added LIPS table so this is now the new link VBAK(VBELN) --> VBAP (VBELN) --> LIPS(VGBEL)

           After execution only SO2 appeared on the query.

     I there a problem with my link to the table? I want the report to still show the Sales Order but since SO1 has no delivery Doc it will just leave the      Delivery No. Column blank.

     How can I do this?

Thanks

[Former Member · Accepted solution]

Hi,

when you link tables, link should be outer join (click on line linking tables and select outer join). System will show both sales orders with and without deliveries.

Regards,

JM

[former_member218251 · Accepted solution]

This is the only right answer.

In the join you should right click on the connecting lines and select Left Outer Join.

To be really honoust: I think when you do not know this, you should first read the manual. There is good documentation available on help.sap.com.

When using Outer Joins, there is a potential risk of stalling the SAP system. When you create a loop, and that is possible, you can create a temporary table in SAP that will fill huge amounts of memory. When the query is then stopped, the roll back can bring down the complete SAP system.

[Jelena · Accepted solution]

H.M Hofman wrote:
To be really honoust: I think when you do not know this, you should first read the manual. There is good documentation available on help.sap.com.

Have you actually read the SAP documentation for Queries? It does not have any simple explanation on left join, from what I can see. Not to mention that calling it "good" is a bit of an overstatement.

This has been asked on SCN before, but unfortunately it's one of the "catch 22" questions where to find existing posts one would already need to know the answer (I haven't been able to find the posts myself without using 'left join').

[former_member218251 · Accepted solution]

I fully agree. You need to know the "outer join" to be able to do the right search.

It might be a good idea to make a blog posting around this. I will put this on my to-do list.

It is a fact, have seen it more then once, that SQVI forms a certain risk when someone does not know what he/she is doing. I have been at one customer where the complete SAP production system has been unavailable for almost a day because of a roll-back. This was in the nineties and with current computing power this will certainly be less then a day, but the risk is still there.

At the other hand, SQVI is such a handy tool, you do want people to use it....

[Jelena · Accepted solution]

There are a few blogs/documents out there on the queries including some on SQVI specifically (this, this and this) but none mentions left join. SQVI is not frequently used because it's limited to one user. We use SQ01/SQ02 for the queries, SQVI is available to IT only for some ad-hoc queries.

I'm not a Basis admin, but have never heard of the rollback issues outside of the update context (what is there to roll back in case of a query?). And one could cause serious performance issues even without a left join. In any case, there are many variables in the performance optimization, so making a statement that one would need to stay away from left join because of some potential memory issue would not be entirely accurate.

If this question has been answered then kindly close it.

[od_manikandan]

Hai Raffy Liboon,

In SQVI, try to create report from reverse document flow.

LIPS-LIKP - VBAP - VBAk

and select outer join between LIKP and VBAP (right cick in SQVI join link, system gives option for LEFT outer join)

This left outer join help to display sales order , even when it don't have subsequent documents.

Reverse order of table should be used in case if you have Batch determination, multiple products in sales order then will not make duplicate sales order output.

Regards,

Mani

[Former Member]

Include VBAP-POSNR with LIPS-VGPOS to your existing connection. Then try the query again. If it does not work, then you may want to use VBFA as suggested by others.

Hope this helps.

Regards,

Mukund S

[siva_vasireddy2]

Hi,

Did you try with VBAF Table

[Former Member]

Hi Siva & Raffy,

The table is VBFA. I guess ,above you have made a typo.

Regards,

Aman