select statement

[Former Member]

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

[RichHeilman]

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

[Former Member]

Hi Rich & Lanka,

Thanks a lot.

points alloted.

cheers

kaki

[Former Member]

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, --<u>Add this field</u>

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 <u>aufk</u> with view <b>CAUFV</b>

select CAUFV_{aufnr afih}revnr CAUFV~RSNUM from <b>CAUFV</b> 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. -- <u>add</u>

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

Hope this may help you.

Lanka