topic select statement in Application Development and Automation Discussions

select statement

Former Member — Mon, 13 Feb 2006 01:57:20 GMT

Hi experts,

Iam trying to get the data like this.


data: begin of t_hdr occurs 0,
        aufnr like aufk-aufnr,
        revnr like afih-revnr,
      end of t_hdr.

data: begin of t_det occurs 0,
        aufnr like resb-aufnr,
        matnr like resb-matnr,
        bdmng like resb-bdmng,
        lgort like resb-lgort,
        maktx like makt-maktx,
        labst like mard-labst,
      end of t_det.

  select aufk~aufnr afih~revnr from aufk inner join afih
             on aufk~aufnr = afih~aufnr into
             corresponding fields of table t_hdr.


  check not t_hdr[] is initial.


  select aufnr matnr bdmng lgort
       into corresponding fields of
       table t_det from resb
       for all entries in t_hdr where aufnr = t_hdr-aufnr.

since RESB table having millions of records,it is taking lot of time.Is there any way to get the data faster?

reward guaranteed

thanks

kaki

Re: select statement

RichHeilman — Mon, 13 Feb 2006 02:18:03 GMT

You will need to make the connection to RESB via the reservation number, RSNUM. You can use the RSNUM from table afko. I pretty sure that it is in AFKO, if not, then it is in AUFK. Please see the updated code below.



data: begin of t_hdr occurs 0,
        aufnr like aufk-aufnr,
<b>        rsnum like afko-rsnum,</b>
        revnr like afih-revnr,
      end of t_hdr.
 
data: begin of t_det occurs 0,
        aufnr like resb-aufnr,
        matnr like resb-matnr,
        bdmng like resb-bdmng,
        lgort like resb-lgort,
        maktx like makt-maktx,
        labst like mard-labst,
      end of t_det.
 
  select aufk~aufnr <b>afko~rsnum</b> afih~revnr 
       from aufk
<b>            inner join afko
             on aufk~aufnr = afko~aufnr</b>
            inner join afih
             on aufk~aufnr = afih~aufnr into
             corresponding fields of table t_hdr.
* You should also probably try to limit this select
* with a where clause
 
  check not t_hdr[] is initial.
 
<b>sort t_hdr ascending by aufnr rsnum .</b>

  select aufnr matnr bdmng lgort
       into corresponding fields of
       table t_det from resb
       for all entries in t_hdr
<b>                where rsnum = t_hdr-rsnum.</b>

Regards,

Rich Heilman

Re: select statement

Former Member — Mon, 13 Feb 2006 02:25:22 GMT

Hi Kaki,

RESB is a very Huge table and you are trying to retrive the data witj out using the key fields.

Please try this :

data: begin of t_hdr occurs 0,

aufnr like aufk-aufnr,

revnr like afih-revnr,

rsnum like resb-rsnum, --Add this field

end of t_hdr.

data: begin of t_det occurs 0,

aufnr like resb-aufnr,

matnr like resb-matnr,

bdmng like resb-bdmng,

lgort like resb-lgort,

maktx like makt-maktx,

labst like mard-labst,

end of t_det.

Replace aufk with view CAUFV

select CAUFV_{aufnr afih}revnr CAUFV~RSNUM from CAUFV inner join afih

on aufk_{aufnr = afih}aufnr into

corresponding fields of table t_hdr.

check not t_hdr[] is initial.

select aufnr matnr bdmng lgort

into corresponding fields of

table t_det from resb

for all entries in t_hdr

where aufnr = t_hdr-aufnr

and RSNUM = t_hdr-rsnum. -- add

RSNUM is a key field in RESEB then it will fetch the data faster.

Hope this may help you.

Lanka

Re: select statement

Former Member — Mon, 13 Feb 2006 02:34:03 GMT

Hi Rich & Lanka,

Thanks a lot.

points alloted.

cheers

kaki