Question on selecting data from multiple tables.

[Former Member]

Hello All,

I tried to create a interactive report with fields from the table VBAP, LIPS, VBUK and VBBE. I fetched SO number, date, delivery and overall status from the table VBAP and VBUK and placed them in the internal table IT. By selection SO date in the selection screen, incomplete order details are displayed.

Now in the output list, by selecting SO number, I want to display the no. of items, net price, delivered quantity and open quantity. I created another internal table to store these values. The first part is working fine. But the problem occurs in the below select query statement.

Case Sy-Lsind.

When 1.

Format Hotspot on.

SELECT VBAP~VBELN VBAP~MATNR VBAP~KWMENG VBAP~NETPR LIPS~LFIMG VBBE~OMENG

FROM VBAP INNER JOIN LIPS ON VBAP~VBELN = LIPS~VBELN

INNER JOIN VBBE ON VBAP~VBELN = VBBE~VBELN

INTO CORRESPONDING FIELD OF TABLE IT1 WHERE VBAP~VBELN = IT-VBELN.

Loop at IT1.

Write: / IT11-VBELN, IT1-MATNR, IT1-KWMENG, IT1-NETPR, IT1-LFIMG, IT1-OMENG.

End Loop.

End Case.

If I click on SO number, it does not go into the secondary list. Please help me to fix this problem.

[Former Member]

Have you read the documentation on the HIDE command?

Rob

[Former Member]

Yes. I used the Hide statement to display the incompleted sale order details from the Internal Table IT.

Loop at IT where GBSTK ne 'c'.

Format Hotspot on.

Write:/ IT-Vbeln, IT-Erdat, IT-Lfstk, IT-Gbstk.

Hide:  IT-Vbeln, IT-Erdat, IT-Lfstk, IT-Gbstk.

Endloop.

[former_member184675]

Hi,

Did you put the "Write" statement on IT1 table in the AT LINE-SELECTION Event?

BR,

Andrei

[Former Member]

Hi Andrei,

Yes. I used the write statement in the loop of IT1 in the AT LINE- SELECTION EVENT. Below is my coding after displaying the values of IT (Please see the reply to Rob). Then I tried to display the values of IT1 in secondary list.

AT LINE-SELECTION.

WINDOW STARTING AT 5 5 ENDING AT 170 45.

CASE SY-LSIND.

* Please see the coding in my first post message.

[Former Member]

How is IT-VBELN declared?

Rob

[Former Member]

DATA: BEGIN OF IT OCCURS 8,

VBELN TYPE VBAK-VBELN,

END OF IT.

This is how 'vbeln' declared in IT.

Rajee

[former_member209818]

can u make sure that data exists in LIPS and VBBE table for your Sales Order? I think data is not present in one of the table and hence join query is failing

[Former Member]

Hi Harshad,

You are right. The table LIPS does not have the record for the selected SO number in the output list. I tried the query statement without the LIPS table & its field. Now it is showing the values of IT in the secondary list. Thank you so much.

So, If data is not existing in the table used in query, Join condition fails. Is there any possibility to get the field blank in secondary list for not existing data?

[former_member184675]

Hi, the problem is in your Select:

SELECT VBAP~VBELN VBAP~MATNR VBAP~KWMENG VBAP~NETPR LIPS~LFIMG VBBE~OMENG

FROM VBAP INNER JOIN LIPS ON VBAP~VBELN = LIPS~VBELN

INNER JOIN VBBE ON VBAP~VBELN = VBBE~VBELN

INTO CORRESPONDING FIELD OF TABLE IT1 WHERE VBAP~VBELN = IT-VBELN.

Join by: VBAP~VBELN = LIPS~VGBEL

And put a check on sy-subrc  after the select.

Regards,

Andrei Sosea

[Former Member]

Thank you. Using sy-subrc is always best to check whether value is in the table. Thank you for letting me know that VGBEL can also be used instead of VBELN.

[Former Member]

Hi Rajee,

May I know why you are using the CASE SY-LSIND. I guess this is causing the problem.

Regards,

Sindhu Pulluru.

[Former Member]

Thank you all. I got the answer why i am not getting the secondary list for the selected sale order. We can let user know about this via passing message.

But is there any possibility to get found values only in secondary list? i.e) In this case, LIPS does not have record, but other tables like VBBE, VBAP have the records for selected SO. Can we get only those in secondary window? If JOIN cannot perform this, how it can be done?

[former_member184675]

You should expand your code logic.

Make the join on VBAP and VBBE and then get the LIPS data into a separate Internal Table (eg. GT_LIPS). Loop on IT1, do a read table on GT_LIPS with VGBEL and VGPOS as foreign keys, and move LFIMG.

By the way, depending on the business logic, you should consider displaying the Picked Quantity instead Delivered Quantity.

Andrei Sosea