What this SQL statement is doing?

[Former Member]

Hi ABAPers,

Can somebody help me in understanding what the below SQL statement is doing..

  SELECT * FROM /BIC/ADSO00 AS tb1

      INNER JOIN /BIC/PMASTER as tbl2

      ON tbl1~field1 = tbl2~field1

      AND tbl12~field2 = tbl2~field2

      INTO CORRESPONDING FIELDS OF TABLE gt_itab

      FOR ALL ENTRIES IN SOURCE_PACKAGE

      WHERE tbl1~field3 = SOURCE_PACKAGE-field3

      AND tbl1~field4 BETWEEN lv_minper AND lv_maxper

      AND tbl1~field5 = SOURCE_PACKAGE-field5

      AND tbl1~field6 = '0100'

      AND tbl2~OBJVERS = 'A'.

thanks in advance !!

Bharath S

[Former Member]

Hi Bharath,

tb1 is your /BIC/ADSO00 table

tbl2 is your  /BIC/PMASTER second table.

It is selecting all contents from /BIC/ADSO00 table available and is selecting respective contents from /BIC/PMASTER second table using field 1 and field2 as key(unique in two tables) and moving into an internal table(structures used in program for holding values similar to DB and can hold values till program execution ends) and selection is valid only for entries in source_package another internal table.

Conditions for the selections are

tbl1~field3 = SOURCE_PACKAGE-field3

      AND tbl1~field4 BETWEEN lv_minper AND lv_maxper

      AND tbl1~field5 = SOURCE_PACKAGE-field5

      AND tbl1~field6 = '0100'

      AND tbl2~OBJVERS = 'A'.

Hope it will be helpful.

Regards,

Kannan

[Former Member]

Hi,

you are selecting two tables, the system will try to fetch records from anyone of the tables(basically CBO will decide which table will be select first and get the row id then will go into another table which will use primary index along with FAE.

you can get execution plan.

1. Activate trace

2. Run your program

3. Deactivate your trace

4. Display trace

    ->Click execution/explain plan. here we can see the order of select.

I think this would be useful for you.

Regards,

Vadamalai A.

[Former Member]

Hi Bharath,

There are 2 table in this select query

1. /BIC/ADSO00 refered as tb1

2.  /BIC/PMASTER refered as tb2

Select query will compare each row of table /BIC/ADSO00 with each row of /BIC/PMASTER to find all pairs of row based on the JOIN condition. (i.e. tbl1~field1 = tbl2~field1 AND tbl1~field2 = tbl2~field2).

Inner join will create a new table by combing the values of both the table based above mentioned condition.

But not all entries are copied to internal table gt_itab.

The no.of entries is restricted for the entries present in SOURCE_PACKAGE.

Later the entries which match the fields in WHERE caluse are copied to gt_itab.

You can refer to SQL - INNER JOINS

to know more about inner joins. Usually FOR ALL ENTRIES clause in SELECT is used to avoid JOINS since it consumes a lot of time.

Regards,

Vedanth

[Former Member]

Hi Jay Bharath,

tb1 and tb2 are the aliases created for tables /BIC/ADSO00 and /BIC/PMASTER respectively.

In the Select statement, you are fetching all the data from these two tables based on the join conditions i.e.

        tbl1~field1 = tbl2~field1 (Note tb1~field1 means /BIC/ADSO00-field1 and the rest follow similarly)

AND tbl12~field2 = tbl2~field2

and then you are placing this data into an internal table.

This select you are performing only for the records that you are getting from SOURCE_PACKAGE based on the selection conditions:

              tbl1~field3 = SOURCE_PACKAGE-field3 

      AND tbl1~field4 BETWEEN lv_minper AND lv_maxper

      AND tbl1~field5 = SOURCE_PACKAGE-field5

      AND tbl1~field6 = '0100'

      AND tbl2~OBJVERS = 'A'.

Hope it helps !!!