classical report

[Former Member]

hi guru's,

i need to write the select statement as nestedselect.and is there any possibility to use for all entries in the following code.if it is then how?wat is the diff b/w inner join and join key word.i need all clarificationc.then full points.

thnx

yogi

tables : lfa1,ekko,ekpo,mara.

parameters : vendor like lfa1-lifnr.

data : begin of itab occurs 0,

lifnr like lfa1-lifnr,

ebeln like ekko-ebeln,

ebelp like ekpo-ebelp,

matnr like mara-matnr,

end of itab.

select lfa1_{lifnr ekko}ebeln ekpo_{ebelp mara}matnr

into table itab

from lfa1

inner join ekko on lfa1_{lifnr = ekko}lifnr

inner join ekpo on ekko_{ebeln = ekpo}ebeln

inner join mara on ekpo_{matnr = mara}matnr

where lfa1~lifnr eq vendor.

loop at itab.

write : / itab-lifnr ,itab-ebeln,itab-ebelp,itab-matnr.

endloop.

[Former Member]

If you want to split up the inner join you can use the FOR ALL ENTRIES addition, but why should you do it. 3 or 4 tables in a inner join is the limit.

What you can do ?

First get all relevant vendors in I_LFA1.

THen read the inner join for EKKO-EKPO and MARA FOR ALL ENTRIES in I_LFA1 where EKKO-LIFNR = I_LFA1-LIFNR.

INNER JOIN and JOIN is exactly the same. Default is INNER JOIN. See also the F1 for JOIN.

regards,

Hans

Please reward all helpful answers !!!!!

[h_senden2]

If you want to split up the inner join you can use the FOR ALL ENTRIES addition, but why should you do it. 3 or 4 tables in a inner join is the limit.

What you can do ?

First get all relevant vendors in I_LFA1.

THen read the inner join for EKKO-EKPO and MARA FOR ALL ENTRIES in I_LFA1 where EKKO-LIFNR = I_LFA1-LIFNR.

INNER JOIN and JOIN is exactly the same. Default is INNER JOIN. See also the F1 for JOIN.

regards,

Hans

Please reward all helpful answers !!!!!

[Former Member]

hi han's

for the code which i h've mentiond , i need to write nested select statement.how can i?

thanx

yogi

[Former Member]

hi han's ,

pls give me the detail code for for all enries so tht i can understand easily.

thanx

loki

[h_senden2]

Please try to avoid nested selects because of bad performance !

The code with FOR ALL ENTRIES is something like :

tables : lfa1,ekko,ekpo,mara.

parameters : vendor like lfa1-lifnr.

data : begin of itab occurs 0,

lifnr like lfa1-lifnr,

ebeln like ekko-ebeln,

ebelp like ekpo-ebelp,

matnr like mara-matnr,

end of itab.

select lifnr into table i_lfa1

from lfa1

where lfa1 eq vendor.

check i_lfa1[] is not initial.

select ekko_{lifnr ekko}ebeln ekpo_{ebelp mara}matnr

into table itab

from ekko

inner join ekpo on ekko_{ebeln = ekpo}ebeln

inner join mara on ekpo_{matnr = mara}matnr

FOR ALL ENTRIES in i_lfa1

where ekko~lifnr eq I_LFA1.

loop at itab.

write : / itab-lifnr ,itab-ebeln,itab-ebelp,itab-matnr.

endloop.

But :

Internal table I_LFA1 only contains one entry, so do not use the I_LFA1 and FOR ALL ENTRIES, but you use :

select ekko_{lifnr ekko}ebeln ekpo_{ebelp mara}matnr

into table itab

from ekko

inner join ekpo on ekko_{ebeln = ekpo}ebeln

inner join mara on ekpo_{matnr = mara}matnr

where ekko~lifnr eq vendor.

regards,

Hans

[Former Member]

Hi Eswar,

<b>In your SELECT query in where clause there is only one condition and for joining the tables we can write nested queries:</b>

You can nest subqueries, that is, the WHERE and HAVING clauses of subqueries can themselves contain a subquery. When a nested subquery in the WHERE clause uses fields from the previous query, it is known as a correlated query. The subquery is then processed for each line of the database table that satisfies the previous condition

<b>

Detail description about For all entries with example:</b>

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

<b>

Reward all helpful answers</b>

Regards,

V.Raghavender.

[Former Member]

one internal table as i_lfa1.

second internal table as i_ekko

select lifnr from lfa1 into i_lfa1

where condition.

if not i_lfa1 is initial.

select a_{ebeln b}ebelp b~matnr from ekpo

into table i_ekko

for all entries in i_lfa1

a as ekko inner join b as ekpo

on a_{ebeln = b}ebeln

where a~lifnr = i_lfa1-lifnr.

endif.