topic Re: Convert nested SQL with Join in Application Development and Automation Discussions

Convert nested SQL with Join

former_member323696 — Fri, 02 Aug 2013 03:35:33 GMT

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.

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 03:46:38 GMT

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.

Re: Convert nested SQL with Join

former_member209120 — Fri, 02 Aug 2013 03:49:59 GMT

Hi Shreenath Bhat,

Try like this

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

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 03:50:50 GMT

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.

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 03:58:35 GMT

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

Re: Convert nested SQL with Join

former_member323696 — Fri, 02 Aug 2013 03:58:50 GMT

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,

Re: Convert nested SQL with Join

arindam_m — Fri, 02 Aug 2013 04:02:29 GMT

Hi,

1 select is always better than 2.

Cheers,

Arindam

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 04:02:30 GMT

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 .

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 04:02:37 GMT

The split will work faster ..

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

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 04:04:42 GMT

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

Re: Convert nested SQL with Join

former_member323696 — Fri, 02 Aug 2013 04:19:27 GMT

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

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

Re: Convert nested SQL with Join

arindam_m — Fri, 02 Aug 2013 04:23:29 GMT

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

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 04:30:31 GMT

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

Re: Convert nested SQL with Join

arindam_m — Fri, 02 Aug 2013 04:36:53 GMT

Hi,

from ekbe as a INNER JOIN ekbe as b

INNER JOIN on same table?

Cheers,

Arindam

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 04:41:19 GMT

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

Re: Convert nested SQL with Join

former_member323696 — Fri, 02 Aug 2013 05:06:58 GMT

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

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 05:11:32 GMT

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

Re: Convert nested SQL with Join

Former Member — Fri, 02 Aug 2013 06:45:14 GMT

No it won't be....