How to change from inner join to for all entries?

[Former Member]

Hi,

                            how to change this code from inner join to for all entries in. could you please suggest me.

SELECT a~vbeln

           a~lifsk

           a~vdatu

           a~bstzd

           a~z2brgew

           a~kunnr

           FROM vbak AS a

           INNER JOIN vbuk AS b

           ON a~vbeln = b~vbeln

           INTO CORRESPONDING FIELDS OF TABLE db_tab

           WHERE  a~vkorg IN vkorg AND

                  a~bstzd IN bstzd AND

                  a~submi IN submi AND

                  a~lifsk IN lifsp AND

                  a~vdatu IN vdatu AND

                  a~vtweg IN vtweg AND


                  a~auart NOT IN ('ZREF', 'ZRED', 'ZRES', 'ZREU' ,'ZREN', 'ZRDO', 'ZORF' ,'ZRF1', 'ZRUD') AND


                   ( ( a~lifsk <> ' ' AND

                      b~abstk <> 'C' AND

                      b~gbstk <> 'C' )   OR ( a~lifsk = ' '  AND

                                            b~cmgst = 'B'  AND

                                            a~vbtyp <> 'H' AND

                                            b~abstk <> 'C' AND

                                            b~gbstk <> 'C' AND

                                            b~gbstk <> ' ') ).

[f_cemunal]

This message was moderated.

[custodio_deoliveira]

This doesn't make any sense.

[matt]

Senseless reply removed.

[Former Member]

Hi Hari,

In the select query, remove the fields and conditions related to table VBUK.

Once the select query on VBAK is executed you will have the data in DB_TAB.

if the table DB_TAB is not initial then proceed with the below steps

Pass the data from table DB_TAB into a dummy table

SORT dummy table using VBELN and DELETE ADJACENT DUPLICATES from dummy table using the field

VBELN

Check the dummy table is initial or not before the select query on VBUK

If this is not empty then process the select query on VBUK into a new internal table DB_VBUK with fields VBELN  and fields  in the where condition in select query above using for all entries from DB_TAB with VBELN else

skip the select query.

if the table DB_VBUK is not initial then

loop DB_TAB and read DB_VBUK based on the DB_TAB-VBELN.

check the  fields DB_VBUK-ABSTK and DB_VBUK-GBSTK  conditions in your select query.

if conditions are successful then it is valid record, populate a new internal table with this record else skip the record and proceed with the next entry.

use the new internal table for further processing.

Hope this solution helps you to resolve the issue. let me know in case of any questions.

regards,

Phani

[custodio_deoliveira]

Hi Hari,

I believe Phani has described very well how to do it. But the bigger questions is: WHY?!?!? You are accessing VBUK with the primary key and I'm pretty sure there's an index for most of the fields you are accessing VBAK, so I can not see any reason to change it from JOIN to FAE. Remember what says: 

Cheers,

Custodio

[matt]

I would imagine that he's been told to redo the query as a FOR ALL ENTRIES, because some ignorant "more experienced" developer has told him that it is better.

FAE is 99.99% of the time slower than an INNER JOIN

There is no restriction on the number of INNER JOINS

FAE is old technology that was replaced by INNER JOIN

There are only a very few specific cases where you must use FAE

Anyone who tells you different doesn't know what they're talking about, and is simply repeating a myth.

[Former Member]

Matthew,

I'm not so sure about your third point:

Matthew Billingham wrote:

FAE is old technology that was replaced by INNER JOIN

FAE is used to use internal tables in conjunction with database tables while a JOIN by desing only affects database tables. To me, these are just different commands for different purposes - none is able to fully replace the other.

  Volker

[f_cemunal]

Hi Billingham,

as i read the quesiton, he is not asking if fae is better or not.he just wants to know how fae works.

as a new learner i would want to try it myself (and thats what i did) and know if fae is better than inner join or not.

i think thats best way to learn something : by yourself, trying making mistakes not by someone telling you thats better or not.

regards.

cem.

[matt]

As I said - there are some specific instances where FAE must be used. One is when you are supplied with an internal table and must do further db reads from that - as is found in BW Start/end routines.

Back in pre-3.1I days, we didn't have JOINS, so we had to use FAE. When joins were introduced, it made life a lot easier - code becomes much simpler. FAE has been around a long time. JOINS were introduced in 3.1I as a more efficient way of reading from multiple database tables. So I stand by my statement - it is old technology.

Most times people here post questions including FAE they are using it as a poor-man's INNER JOIN. That needs to be educated out of the ABAP community.

[matt]

Maybe he is. That doesn't alter that fact that FAE is usually bad programming practice.

[f_cemunal]

i am not arguing with you on that.no one here does.

[Former Member]

Cem Unal wrote:

as a new learner i would want to try it myself (and thats what i did) and know if fae is better than inner join or not.

Hi Cem,

Your statement above is where so much harm can be done.  I'm all for learning by yourself however some things should just be accepted, where the evidence and experience of the wider community all says one thing*.

Saying "I wanted to see if walking on the seabed was possible", "I wondered if driving at 100mph through the middle of a city would be safe" or "I wonder if FAE is better than inner join" are all equally pointless.  As a learner, you aren't really gaining anything other than a way to lose your valuable time.  We see so many people posting on these forums who are happy to argue and question everything but just because some supposed "expert" they have met says FAE is better than inner join, no amount of explanation can convince them otherwise.

Experience, fact and existing rules/guidelines/knowledge clearly answer the questions.  There are far too many people "new to ABAP" that are taught terrible practices, and FAE vs Inner Join is one of the biggest offenders.  Sadly, it doesn't seem to matter how many times it is pointed out.

My suggestion to learners is to accept the general advice from the more experienced people on these forums and instead focus your efforts on an area or skill that is valuable and will help you develop your career.

As an example, I'd suggest all of the "new to ABAP" brigade learn how to question their team leads and/or trainers who continuously perpetuate the myth that FAE is better than inner join, usually without backing it up with any evidence.  The ability to question unsubstantiated facts in the world of SAP will serve you very well.

And as for the OP not asking about which is better, I'd be willing to bet a sum of money they are only asking this question because someone has told them they should use FAE instead of inner-join, probably following a code quality review...

Cheers,

G.

* It's weird that FAE vs inner join has created a divide between developers and actually we don't have everyone saying the same thing - we have the camp of people who understand how it all works and try to educate everyone with evidence, facts, experience, etc and the other camp who preach blindly, dismissing any counter-argument but never actually produce any evidence to back themselves up.  Sounds similar to so many of the worlds problems actually...

Your attitude of wanting to learn and understand is admirable and definitely the right way to approach learning SAP - just make sure you also develop your ability to judge people at the same time as learning the technical arts of ABAP.  Knowing when a supposed "expert" is talking rubbish will get you a long way in your career

[matt]

This should be a blog in its own right!

[Former Member]

This is how most of my blog posts start, except this time I couldn't be bothered to move it (at the time) and it ended up here.

I do now have a draft blog post in progress thought...

Cheers,

G.

[UweFetzer_se38]

Can I copy/paste your answer to this thread ?