abap select query problem

[Former Member]

This report will take delivery document number and delivery date from user and fetches details from delivery table and fetches

corresponding sales order details and billing details and displays sales order details with ALV list.

<< Please only post the relevant portions of your code >>

i just want to know that is this way of fetching the data into internal table okay ?are there any better ways of fetching the data into the internal table ? why is it looping in work area of lips, why not in likp?

(plz dont use field-symbols or oo abap or macros) I WANT TO USE PLAIN AND SIMPLE ABAP STATEMENTS LIKE ABOVE..

By using "vbeln type lips-vbeln" are we refering to the field or data element? plz suggest.

P.S. [my approach was to inner join likp and lips into itab(my internal table).

select data from kna1, vbak,vbap,vbrp into respective internal tables like it_kna1,it_vbak etc.

then using individual loops into the tables, i would use read table to insert data into itab(my final internal table) plz suggest which method wud be more efficient? ]

Edited by: Rob Burbank on Jun 8, 2009 11:54 AM

[Former Member]

Hi ,

join on cluster table with no proper indexing or unsfficient "where" condition; is never advisable ..

join on more then 4 .. even 4 are more ... can really effect the processing time .

so to avoid it ..

we usually fetch all the recorrds in the itab and do a read statement on it,cause operation on itab is any time faster ..

so i suggest u understand first the tables u are using and then approach accordingly ..

Regards

Renu .

[Former Member]

ok i am posting the select queries once again.

i want to know why are we looping in t_lips and why not t_likp? bcoz tlikp is the header table, if we loop thru it and then read the rest tables then what will be the problem? plz clarify with examples._

[my idea was to loop at t_likp then read t_lips.append the lips data into t_order(main internal table).then do read table on t_kna1,t_vbap etc indexing t_order. so plz suggest whether my approach was write or wrong?if wrong why?plz site any other ways of doing this query.

-

---

types: begin of ty_vbap,

vbeln type vbap-vbeln,

posnr type vbap-posnr, .....

does the declaration statement in types refer to tables or data elements? what is the difference if we declare it like:

types: begin of ty_vbap,

vbeln type vbeln,

posnr type posnr, .....

**********************************************

select-options:

s_deldoc FOR likp-vbeln, " Delivery

s_dldate FOR likp-lfdat. " Delivery Date

*************************

• Get delivery document number,delivery date,customer number from

• delivery header table

SELECT vbeln " Delivery

lfdat " Delivery Date

kunnr " Customer Number 1

FROM likp

INTO TABLE t_likp

WHERE vbeln IN s_deldoc

AND lfdat IN s_dldate.

IF sy-subrc EQ 0.

• Get Customer name for customer numbers from Customer master table

SELECT kunnr " Customer Number 1

name1 " Name 1

FROM kna1

INTO TABLE t_kna1

FOR ALL ENTRIES IN t_likp

WHERE kunnr EQ t_likp-kunnr.

IF sy-subrc EQ 0.

• Get delivery item number,sales document number,sales item number,

• delivery quantity from delivery item table

SELECT vbeln " Delivery

posnr " Delivery Item

vgbel " Document number of

" reference document

vgpos " Item number of reference item

lfimg " Actual quantity delivered

vrkme " Sales unit

FROM lips

INTO TABLE t_lips

FOR ALL ENTRIES IN t_likp

WHERE vbeln EQ t_likp-vbeln.

IF sy-subrc EQ 0.

• Get sales document number,item number,material,material description,

• ordered quantity from sales item table

SELECT vbeln " Sales Document

posnr " Sales Document Item

matnr " Material Number

arktx " Short text for sales order

" item

kwmeng " Cumulative Order Quantity

vrkme " Sales unit

FROM vbap

INTO TABLE t_vbap

FOR ALL ENTRIES IN t_lips

WHERE vbeln EQ t_lips-vgbel

AND posnr EQ t_lips-vgpos.

IF sy-subrc EQ 0.

• Get sales document number ,created date,purchase order number from

• sales header table

SELECT vbeln " Sales Document

erdat " Date on Which Record Was" Created

aufnr " Order Number

FROM vbak

INTO TABLE t_vbak

FOR ALL ENTRIES IN t_lips

WHERE vbeln EQ t_lips-vgbel.

IF sy-subrc EQ 0.* Get billing document number,billing item,reference delivery document

• number,delivery item number,billing item from billing item table

SELECT vbeln " Billing Document

posnr " Billing item

vgbel " Document number of the

" reference document

vgpos " Item number of the" reference" item

fklmg " Billing quantity in" stockkeeping unit

vrkme " Sales unit

FROM vbrp

INTO TABLE t_vbrp

FOR ALL ENTRIES IN t_lips

WHERE vgbel EQ t_lips-vbeln

AND vgpos EQ t_lips-posnr.

ENDIF. " IF SY-SUBRC EQ 0

ENDIF. " IF SY-SUBRC EQ 0

ENDIF. " IF SY-SUBRC EQ 0

ENDIF. " IF SY-SUBRC EQ 0

ELSE.

• Display message if records are not found for entered values

MESSAGE S000.

EXIT.

ENDIF. " IF SY-SUBRC EQ 0

• Looping Delivery item internal table to assign values to order

• internal table

LOOP AT t_lips INTO fs_lips.

• Get delivery date and customer number for delivery document number

• from delivery header internal table

READ TABLE t_likp WITH KEY vbeln = fs_lips-vbeln

INTO fs_likp.

• Get customer name for customer number from customer master internal

• table

IF sy-subrc EQ 0.

READ TABLE t_kna1 WITH KEY kunnr = fs_likp-kunnr

INTO fs_kna1.

• Get sales document number,item number,ordered quantity for delivery

• document number,item number from sales item internal table

IF sy-subrc EQ 0.

READ TABLE t_vbap WITH KEY vbeln = fs_lips-vgbel

posnr = fs_lips-vgpos INTO fs_vbap.

• Get goods issue date and purchase order number for sales document

• number from sales header internal table

IF sy-subrc EQ 0.

READ TABLE t_vbak WITH KEY vbeln = fs_vbap-vbeln INTO fs_vbak.

IF sy-subrc EQ 0.

• Get billing document number,billing item,billing quantity for delivery

• document number,delivery item number from billing item internal table

READ TABLE t_vbrp WITH KEY vgbel = fs_lips-vbeln

vgpos = fs_lips-posnr INTO fs_vbrp.

• Assign sales,delivery,billing fields into respective fields of sales

• order internal table

IF sy-subrc EQ 0.

fs_order-vbeln = fs_vbap-vbeln.

fs_order-posnr = fs_vbap-posnr.

fs_order-erdat = fs_vbak-erdat.

fs_order-kunnr = fs_likp-kunnr.

fs_order-name1 = fs_kna1-name1.

fs_order-aufnr = fs_vbak-aufnr.

fs_order-matnr = fs_vbap-matnr.

fs_order-arktx = fs_vbap-arktx.

fs_order-kwmeng = fs_vbap-kwmeng.

fs_order-vrkme = fs_vbap-vrkme.

fs_order-vbeln1 = fs_lips-vbeln.

fs_order-posnr1 = fs_lips-posnr.

fs_order-lfimg = fs_lips-lfimg.

fs_order-vrkme1 = fs_lips-vrkme.

fs_order-vbeln2 = fs_vbrp-vbeln.

fs_order-posnr2 = fs_vbrp-posnr.

fs_order-fklmg = fs_vbrp-fklmg.

fs_order-vrkme2 = fs_vbrp-vrkme.

APPEND fs_order TO t_order.

CLEAR fs_order.

ENDIF. " IF SY-SUBRC EQ 0

ENDIF. " IF SY-SUBRC EQ 0

ENDIF. " IF SY-SUBRC EQ 0

ENDIF. " IF SY-SUBRC EQ 0

ENDIF. " IF SY-SUBRC EQ 0

ENDLOOP. " LOOP AT T_LIPS INTO FS_LIPS