topic Re: Regarding JOIN Statement in Application Development and Automation Discussions

Regarding JOIN Statement

former_member267445 — Wed, 01 Aug 2012 06:21:13 GMT

Hi Gurus,

I am working for change TR. I need to get the data from 2 tables and the table are VBEP and VBBE. Scenario is business will create some orders(future orders) with Schedule line category with out confirmed quantity, so those orders(future orders) will not store in VBBE. After confirmed some quantity the schedule line item category will change then those orders will store in VBBE. But business needs both the orders which stored in VBEP and VBBE.

Here the fields in selection screen are Material Number, Plant, Customer Number, these are not in VBEP but in VBBE.

Previously the report was developed by using selection from VBBE but in output those store in VBEP orders(future orders) are in not displaying.

Please light me how to join these tables.

Thanks and Regards,

Muralikrishna Peravali

Re: Regarding JOIN Statement

Kartik2 — Wed, 01 Aug 2012 06:56:20 GMT

Hi,

I think the BAPI 'BAPI_SALESORDER_GETLIST' will work for you. Please try it and let us know. Thank you.

Regards,

Kartik

Re: Regarding JOIN Statement

former_member267445 — Wed, 01 Aug 2012 07:14:46 GMT

But i want to implement this query in already existing Z report without disturbing functionality.. Can I?

Re: Regarding JOIN Statement

ThomasZloch — Wed, 01 Aug 2012 08:31:05 GMT

So there is always an entry in VBEP but not always in VBBE? Then try a LEFT JOIN between these two tables with VBEP as leading table, linked by the identical primary key.

Recent similar discussion:

Thomas

Re: Regarding JOIN Statement

former_member267445 — Wed, 01 Aug 2012 09:16:06 GMT

Hi Thomas,

Thank you for reply. But i need the data from VBBE also. As per my knowledge if we use LEFT JOIN then right side table data wont get if there is no similar record. Correct?

Regards,

Muralikrishna

Re: Regarding JOIN Statement

ThomasZloch — Wed, 01 Aug 2012 10:21:24 GMT

Correct, but the VBEP data would be there. What data would you expect if there is no linked VBBE record?

Maybe I did not understand your scenario correctly. Are you saying there might be entries in VBBE for an order item but not in VBEP? It's been a while since I worked in SD, is this possible at all considering the application logic?

The cardinality must be clear, only then a working solution can be proposed.

Thomas

Re: Regarding JOIN Statement

former_member267445 — Wed, 01 Aug 2012 13:50:19 GMT

Hi Thomas,

Actually the scenario is.. we have future order concept in SD. for Future order they created schedule line category type as ZF, when they create the order with ZF then the order is not updated in VBBE table. But the order is in VBEP with ETTYP = 'ZF'.

If any user confirms the quantity then schedule line category converted as CP that means MRP, the order with CP is stored in VBBE. Business wants in output the orders which have ZF and as well as CP.

Thanks and Regards,

Muralikrishna Peravali

Re: Regarding JOIN Statement

ThomasZloch — Wed, 01 Aug 2012 14:15:29 GMT

In the second case, is there still an entry in VBEP, in addition to VBBE? (I assume so)

If yes, you can go back to my first suggestion, select VBEP and left join VBBE based on VBELN, POSNR and ETENR.

Thomas

Re: Regarding JOIN Statement

former_member267445 — Wed, 01 Aug 2012 14:44:14 GMT

Hi Thomas,

If i write, select from VBEP left outer join VBBE. Then I am not able to write the conditions in WHERE clause using VBBE. Here I enclosed the query..

Thanks and regards,

Muralikrishna.

Re: Regarding JOIN Statement

alex_campbell — Wed, 01 Aug 2012 19:47:24 GMT

If you want to select orders that have type ZF, than it does not make sense to include any conditions for table VBBE in the WHERE clause, because orders that have type ZF will not have any value for those fields.

It sounds like you are trying to select two exclusive datasets. Records where VBEP~ETTYP = 'ZF', and records where VBEP~ETTYP = 'CP' AND VBBE~??? = the value you allude to needing to check. You could break it into two different select statements (one for ZF, the other for CP), and use APPENDING TABLE instead of INTO TABLE

Re: Regarding JOIN Statement

JackGraus — Thu, 02 Aug 2012 02:40:04 GMT

To me it looks like that data that is comming from VBBE (Material numer, Plant, customer number) should be get from other tables in case there is no VBBE entry for a VBEP schedule line. These tables are VBAK (Order header) and VBAP (Order item). When you have a VBEP schedule line there is always an order VBAK and order line VBAP.

Regards Jack

Re: Regarding JOIN Statement

former_member267445 — Mon, 06 Aug 2012 15:21:41 GMT

Hi Jack,

Sorry for late reply, I was on leave. I have quantity fields that should get from VBBE only. I searched related fields but i didnt find.

Regards,

Muralikrishna

Re: Regarding JOIN Statement

former_member267445 — Mon, 06 Aug 2012 15:23:44 GMT

Hi Thomas,

I tried usinf left join, till the orders are not coming whose type is ETTYP = 'ZF'.

Regards,

Muralikrishna