topic classical report in Application Development and Automation Discussions

classical report

Former Member — Tue, 01 May 2007 07:06:22 GMT

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.

Re: classical report

h_senden2 — Tue, 01 May 2007 07:11:08 GMT

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 !!!!!

Re: classical report

Former Member — Tue, 01 May 2007 07:17:05 GMT

hi han's

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

thanx

yogi

Re: classical report

Former Member — Tue, 01 May 2007 07:18:34 GMT

hi han's ,

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

thanx

loki

Re: classical report

h_senden2 — Tue, 01 May 2007 07:45:10 GMT

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

Re: classical report

Former Member — Tue, 01 May 2007 07:54:46 GMT

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.

Re: classical report

Former Member — Tue, 01 May 2007 08:04:06 GMT

Hi Eswar,

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

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

Detail description about For all entries with example:

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

Reward all helpful answers

Regards,

V.Raghavender.