topic Re: is this correct procedure..? in Application Development and Automation Discussions

is this correct procedure..?

Former Member — Thu, 26 Jun 2008 04:32:47 GMT

select vbap~vbeln

vbap~posnr

vbap~matnr

vbap~ps_psp_pnr

kna1~kunnr

kna1~land1

konh~kosrt

konh~knumh

into corresponding fields of

table itab1

from kna1 join vbak

on VBAK_{KUNNR = KNA1}KUNNR join vbap

on vbap_{vbeln = vbak}vbeln join a090

on a090_{vbeln = vbap}vbeln

and

a090_{posnr = vbap}posnr

and

a090_{matnr = vbap}matnr join konh

on a090_{knumh = konh}knumh join konp

on konh_{knumh = konp}knumh

where a090~kappl = 'V'

and a090~kschl = 'PR00'

and konh~kosrt in srchterm

and konp~loevm_ko = 'X' and

vbak~vbtyp = 'C' and

vbap~pstyv = 'TAO' and

vbak~erdat in date.

this coding procedure is correct or not..?

Re: is this correct procedure..?

Former Member — Thu, 26 Jun 2008 04:38:20 GMT

HI,

Don't use more than two tables in a join. It will reduce performance.

Use first two tables in one join then use that internal table and FOR ALL ENTRIES to fetch entries from the thrid table.

Loop through first table, read the second table's matching entry, move all corresponding entries to a new final internal table.

Regards,

Subramanian

Re: is this correct procedure..?

Former Member — Thu, 26 Jun 2008 04:44:49 GMT

hi,

1. Use FOR ALL ENTRIES statement instead of join.

2. Use INTO TABLE statement instead of INTO CORRESPONDING FIELDS OF TABLE statement

http://www.sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm

Regards,

Santosh

Re: is this correct procedure..?

Former Member — Thu, 26 Jun 2008 04:50:46 GMT

Hi,

check the following points

1. do't use more then 2 tables in the joining.

2. if you are using join statement, check the joinning fields should be key fields only.

in your query,

1. first join VBAK and VBAP table using vbeln.

2. retrieve the additional data from kna1 based on kunnr

3. join knop and konh table.

3. retrieve the additional data from A090

Reward if found helpful.

Regards,

Re: is this correct procedure..?

Former Member — Thu, 26 Jun 2008 04:52:05 GMT

select vbap~vbeln

vbap~matnr

vbap~posnr

vbap~pstyv

vbap~ps_psp_pnr

vbak~erdat

vbak~vbtyp

vbak~kunnr

kna1~land1

a090~kappl

a090~kschl

a090~knumh

konh~kosrt

konp~loevm_ko

into corresponding fields of

table itab1

from vbap inner join vbak on vbap_{vbeln = vbak}vbeln

inner join kna1 on VBAK_{KUNNR = KNA1}KUNNR

inner join a090 on ( a090_{vbeln = vbap}vbeln

and a090_{posnr = vbap}posnr

and a090_{matnr = vbap}matnr )

inner join konh on a090_{knumh = konh}knumh

inner join konp on konh_{knumh = konp}knumh

where a090~kappl = 'V'

and a090~kschl = 'PR00'

and konh~kosrt in srchterm

and konp~loevm_ko = 'X' and

vbak~vbtyp = 'C' and

vbap~pstyv = 'TAO' and

vbak~erdat in date.

This code should work..

Better to avoid this kind of lenghy joins , since if there is no entries for any one of table the sy-subrc value will be 4.

Regards,

Sreeja