Saturday, May 07, 2011

Rewriting a select statement with into corresponding fields

One of my task in my recent work was to optimize a select statement. As a best practice, using asterisk (*) and into corresponding is greatly discouraged in a select statement. One of our tasks today, is to optimize the code below and I will show you how.

    SELECT alebn alebz aworg aworg_rev awref_rev awsys awtyp beknz belnr beltp blart bldat
           bltxt btrkl budat bukrs buzei bwstrat bw_refbz cpudt cputm ctyp1 ctyp2 ctyp3
           ctyp4 dabrz delbz ebeln ebelp erlkz fkber geber gjahr gkoar gkont grant_nbr gsber
           hrkft kokrs kstar kurst kwaer k_gjahr k_timestmp k_versn k_vrgng lednr logsyso
           logsysp logsystem mandt matnr mbfbtr mbgbtr mefbtr megbtr meinb meinh mvflg objnr
           objnr_hk objnr_n1 objnr_n2 objnr_n3 orgvg owaer pafbtr pagbtr paobjnr pargb parob
           parob1 pbukrs perab perbi perio pernr pfkber pgeber pgrant_nbr pkstar pscope psegment
           qmnum rbest refbk refbn refbt refbz refbz_fi refgj scope segment sgtxt stflg stokz
           sumbz timestmp twaer usnam uspob varnr vbund versn vrgng werks wkfbtr wkgbtr wogbtr
           wrttp wsdat wtgbtr zekkn zlenr zprofit_center zzcommcode zzfuncaccnt zztxtid
           zzwriteofcat zz_ext_locno zz_matnr zz_pernr zz_xref3
                                                       WHERE kokrs = c_ko01 AND
                                                         belnr BETWEEN r_belnr-low AND r_belnr-high AND
                                                             lednr = c_lednr_00 AND
                                                             objnr IN r_bsis_objnr AND
                                                             gjahr IN r_gjahr AND
                                                             wrttp IN r_wrttp AND
                                                             versn = c_vers_000 AND
                                                             bukrs IN r_bukrs AND
                          ( beknz <> c_debit_a OR 
                        ( wrttp = c_vtype_04 AND wrttp = c_vtype_12 ) ).  

Of course, the answer for this is by simply matching the fields of covp with the fields of tb_covp structure. So the only thing that we need to do is by copying the fields of covp and arranging it in order. Don't forget also to consider the secondary and primary fields used in the where clause above.

This is how are we going to copy them all with no sweat.

 In our case, we will use se16. Just follow the pictures below.

1. We need to get the fieldnames contained in tb_covp (defined by cobk and kaep_coep_x structures).

2. Put at least the maximum width to show all fields and 1 for the max hits for us to be able to speed up our process.

3. Select a record and then click the show details button (with lens).

4. Press CTRL + Y then drag your mouse up to the last field and copy. This is also the best way in copying the fields of a table, esp. those who have lots of fields.

5.  Assuming tb_covp has the same structure as table covp, then we can replace the fields directly in our select statement and voila!

No comments:

Post a Comment