topic Re: Question on selecting data from multiple tables. in Application Development and Automation Discussions

Question on selecting data from multiple tables.

Former Member — Tue, 17 Apr 2012 16:13:00 GMT

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.

Re: Question on selecting data from multiple tables.

Former Member — Tue, 17 Apr 2012 16:26:59 GMT

Have you read the documentation on the HIDE command?

Rob

Re: Question on selecting data from multiple tables.

Former Member — Tue, 17 Apr 2012 16:45:19 GMT

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.

Re: Question on selecting data from multiple tables.

former_member184675 — Tue, 17 Apr 2012 17:11:44 GMT

Hi,

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

BR,

Andrei

Re: Question on selecting data from multiple tables.

Former Member — Tue, 17 Apr 2012 17:14:39 GMT

How is IT-VBELN declared?

Rob

Re: Question on selecting data from multiple tables.

Former Member — Wed, 18 Apr 2012 11:08:04 GMT

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.

Re: Question on selecting data from multiple tables.

Former Member — Wed, 18 Apr 2012 11:09:56 GMT

DATA: BEGIN OF IT OCCURS 8,

VBELN TYPE VBAK-VBELN,

END OF IT.

This is how 'vbeln' declared in IT.

Rajee

Re: Question on selecting data from multiple tables.

former_member209818 — Wed, 18 Apr 2012 11:15:09 GMT

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

Re: Question on selecting data from multiple tables.

former_member184675 — Wed, 18 Apr 2012 11:30:35 GMT

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

Re: Question on selecting data from multiple tables.

Former Member — Wed, 18 Apr 2012 11:32:00 GMT

Hi Rajee,

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

Regards,

Sindhu Pulluru.

Re: Question on selecting data from multiple tables.

Former Member — Wed, 18 Apr 2012 11:32:25 GMT

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?

Re: Question on selecting data from multiple tables.

Former Member — Wed, 18 Apr 2012 11:54:00 GMT

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.

Re: Question on selecting data from multiple tables.

Former Member — Wed, 18 Apr 2012 12:01:51 GMT

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?

Re: Question on selecting data from multiple tables.

former_member184675 — Wed, 18 Apr 2012 12:34:44 GMT

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