Convert nested SQL with Join

[former_member323696]

Hi All,

I need to convert below nested SQL with join

select ebeln BELNR GJAHR into CORRESPONDING FIELDS OF is_ekbe1 from ekbe where ebeln = ( select ebeln from ekbe where belnr EQ gs_mkpf-mblnr AND gjahr EQ gs_mkpf-mjahr ).

I have done as below but i am not getting expected output.

select a~ebeln a~BELNR a~GJAHR from ekbe as a INNER JOIN ekbe as b

                    on a~ebeln = b~ebeln

                    into CORRESPONDING FIELDS OF TABLE it_ekbe1

                     where a~belnr EQ gs_mkpf-mblnr AND a~gjahr EQ gs_mkpf-mjahr.

Regards.

[Former Member]

You have the wrong alias....

select a~ebeln a~BELNR a~GJAHR from ekbe as a INNER JOIN ekbe as b
                    on a~ebeln = b~ebeln
                    into CORRESPONDING FIELDS OF TABLE it_ekbe1
                     where b~belnr EQ gs_mkpf-mblnr AND b~gjahr EQ gs_mkpf-mjahr.

[former_member209120]

Hi Shreenath Bhat,

Try like this

select a~ebeln a~BELNR a~GJAHR from ekbe as a INNER JOIN ekbe as b

                    on a~ebeln = b~ebeln

                    into CORRESPONDING FIELDS OF TABLE it_ekbe1

                     where a~belnr EQ gs_mkpf-mblnr AND a~gjahr EQ gs_mkpf-mjahr.

                    where b~belnr EQ gs_mkpf-mblnr AND b~gjahr EQ gs_mkpf-mjahr.

[Former Member]

It would be better if you split the select as the above query results in 1 output only.

select single ebeln from ekbe into lv_ebeln

                                            where belnr EQ gs_mkpf-mblnr

                                              AND gjahr EQ gs_mkpf-mjahr.

IF sy-subrc is initial.

select ebeln BELNR GJAHR into CORRESPONDING FIELDS OF is_ekbe1

                                             from ekbe where ebeln = lv_ebeln.

Endif.

[Former Member]

Hi,

I would prefer

select single ebeln belnr gjahr from ekbe into CORRESPONDING FIELDS OF is_ekbe1

                                            where belnr EQ gs_mkpf-mblnr

                                              AND gjahr EQ gs_mkpf-mjahr.

Regards,

Klaus

[former_member323696]

Hi Mohammed Yakub Shah,

Thanks for your input. it is working .

You have suggested to split the SQL. i wanted to know which method will give the better prformance, the one with JOIN and splited SQL.

Regards,

[arindam_m]

Hi,

1 select is always better than 2.

Cheers,

Arindam

[Former Member]

The split will work faster ..

Unless you have a for all entries that is the first select resulting in more than 1 PO Number

[Former Member]

But join will consume more time in this case as the resultant is only 1 record I suppose.

Please correct me sir  if I am mistaken

[former_member323696]

• Sorry, I have not posted complete block. The block is as
  below.

LOOP AT gt_mkpf INTO gs_mkpf.



select a~ebeln a~BELNR a~GJAHR a~BEWTP from ekbe as a INNER JOIN ekbe as b



on a~ebeln = b~ebeln



into CORRESPONDING FIELDS OF TABLE it_ekbe1



where b~belnr EQ gs_mkpf-mblnr AND b~gjahr EQ gs_mkpf-mjahr.







APPEND LINES OF it_ekbe1 to it_ekbe.



ENDLOOP.

And i need output something like

ROW| EBELN| EBELP| ZEKKN| VGABE| GJAHR|
BELNR |BUZEi| BEWTP|

1| 4500030722| |0 |0 | |2012 |5000028540|
|0| E|

2| 4500030722| |0 |0 | |2012 |5105603280|
|0| Q|

Please let me know in the above case which
is recomended, Whether split or single sql

Regards

[arindam_m]

Hi,

First no sir plz... I would go by the SQL Traces more if I have to compare. But basic steps involved in Oracle query execution is (unless they are cached).

A syntax check - Are all keywords present
A semantic check against the dictionary - Are all table names are correct
The creation of the cost-based decision tree of possible plans
The generation of the lowest cost execution plan
Binding the execution plan - This is where the table--> tablespace --> datafile translation occurs.
Executing the query and fetching the rows. 

So it depends if a SELECT SINGLE takes more time when compared to time taken for  the steps from 1 to 5.

Cheers,

Arindam

[Former Member]

Well if you cannot group MKPF in the query try that and this will kill your system rather use a FOR ALL ENTRIES.

select a~ebeln a~BELNR a~GJAHR a~BEWTP from ekbe as a INNER JOIN ekbe as b

on a~ebeln = b~ebeln

into CORRESPONDING FIELDS OF TABLE it_ekbe

FOR ALL ENTRIES IN gt_mkpf

where b~belnr EQ gt_mkpf-mblnr AND b~gjahr EQ gt_mkpf-mjahr.

This will give in one shot.

I would recommend to add MKPF also to this only with a join if you can... as even FOR ALL ENTRIES is costlier still it is better than a nested loop.

I also came to know this lately by

[arindam_m]

Hi,

from ekbe as a INNER JOIN ekbe as b

INNER JOIN on same table?

Cheers,

Arindam

[former_member323696]

Hi,

The number of gt_mkpf in the real production secenario is in terms of laks or 10 Laks. Still it is recomended to use FOR ALL ENTRIES than the loop ?

Regards

[Former Member]

You will hit it 10 Lakh times in loop endloop.

If needed create a copy of your program and put for all entries in it.

and run 3 times each program and evaluate the results in SE30 / SAT.

For how to use run time analysis

http://scn.sap.com/docs/DOC-10248

[Former Member]

Hi !

Do you need to write JOIN ??

I guess the output will be same with below query as well.

select ebeln BELNR GJAHR

   into CORRESPONDING FIELDS OF is_ekbe1

   from ekbe where belnr EQ gs_mkpf-mblnr AND gjahr EQ gs_mkpf-mjahr .

[Former Member]

No it won't be....

[Former Member]

Hi shreenath,

U can try with the below code.

select a~ebeln

          a~BELNR

          a~GJAHR

          B~MJAHR

          into CORRESPONDING FIELDS OF TABLE it_ekbe1

          from ekbe as a INNER JOIN mkpf as b

          on a~GJAHR = b~MJAHR

          where a~belnr EQ mkpf-mblnr

          AND b~MJAHR EQ mkpf-mjahr.

Regards,

Santhosh