topic Select Query using table aliasing in Application Development and Automation Discussions

Select Query using table aliasing

ricky_shaw — Fri, 19 Aug 2011 14:06:21 GMT

Hello,

I have 2 select queries as below.

I want to AVOID hitting the EVER table twice .

get all the contracts & CA for the Move Out date from EVER.

1) select * from ever into table it_ever

where auszdat in s_modate.

2) if not it_ever is initial.

select * from dfkkop into table it_dfkkop

for all entries in it_ever[]

where hvorg = '0200'

and tvorg = '0002'

and vkont = it_ever-vkonto. "s_vkont

and augst ne '9'
and vtref = it_ever-contr1.

endif.

3) if it_dfkkop[] is not initial.

select * from ever into table it_ever1

for all entries in it_dfkkop

where vkonto = it_dfkkop-vkont

and einzdat ge sy-datum "commented temporarily

and auszdat gt sy-datum.

Can anyone suggest me to combine 1) & 3) into one select query. Probably we need to use table aliasing which i dont know.

Sorry that i dont know how to use code tags

Thanks

Ricky

Re: Select Query using table aliasing

Sandra_Rossi — Fri, 19 Aug 2011 16:28:07 GMT

Hi Ricky,

I don't think you have to combine 1 and 3. You just need to replace the 3 by a LOOP AT / DELETE to remove all entries of IT_EVER that do not have a corresponding entry in IT_DFKKOP.

Sandra

Re: Select Query using table aliasing

Former Member — Fri, 19 Aug 2011 21:07:51 GMT

Hello,

In my opinion an inner join can do that work also...just trying to combine 1 to 3...

select * into table it_ever 
from ever as i
 inner join dfkkop as d
on i~vkont = d-vkonto
where i~auszdat in s_modate
     and d~hvorg = '0200'
     and d~d!tvorg = '0002'.

if sy-subrc eq 0.
 delete it_ever where auszdat gt sy-datum.
endif.

Can you please try this?

Re: Select Query using table aliasing

Clemenss — Fri, 19 Aug 2011 22:39:26 GMT

Hi Ricky,

the task is not the tables, the task is to identify which fields you need from which table

select dfkkop~gpart dfkkop~vkont dfkkop~bukrs dfkkop~betrw ... ever~einzdat ever~auszdat...
  into corresponding fields of table <your result table>
   from dfkkop 
  join ever
    on ever~contr1 = dfkkkop~vtref and 
       ever~vkonto =  dfkkkop~vkont
   where hvorg = '0200'
     and tvorg = '0002'
     and ever~einzdat ge sy-datum "commented temporarily
     and ever~auszdat gt sy-datum.

Don't worry about code tags: Select the code with the mouse and hit the <> button above. Or write before and after the code.

Regards,

Clemens

Re: Select Query using table aliasing

ricky_shaw — Tue, 23 Aug 2011 21:01:30 GMT

Hi All,

Thanks for the replies . But what i want to ask you is that:

fact : Generally, a VKONT in table DFKKOP has more entries contracts in EVER table.

1) I need to first get all the VKONTs(Contract Accounts) & Vertrags (Contracts) from EVER table.

2) And then based above fact i will get all Contracts in EVER after passing the VKONT (which we got from EVER).

3) So i want to know if i can write some Table Aliasing while hitting DFKKOP itself (as below) to avoid a second query for EVER table.

 select a~vkont a~vtref a~betrh from dfkkop into table it_dfkkop
for all entries in it_ever[]
where hvorg = '0200'
and tvorg = '0002'
and vkont = it_ever-vkonto. "s_vkont
 and augst ne '9'
 and a~vkont = it_ever-contr1. --> can i write like this
endif.

i want to include the result set again as a source for where condition. Hope i am clear. This is possible using table aliasing but i do not know aout that.

Pls suggest.

Thanks

Ricky

Re: Select Query using table aliasing

Subhankar — Wed, 24 Aug 2011 04:13:14 GMT

just do the inner join between these two tables, with condition

ever-vkonto = dfkkop-vkont

ever-vkont = dfkkop-vkont

where ever-auszdat in s_modate

and dfkkop-hvorg = '0200'

and dfkkop-tvorg = '0002'

then from the internal table delete records where auszdat gt sy-datum.

This solve your problem in one select. No need to do three select.

N.B: Please check the performance.(some times three select are much faster than one select with join condition)

Thanks

Subhankar