Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

change internal table column data

0 Kudos
799

hi guru,

My problem is that i want to display PO Qty and Scheduled Quantity in alv i used join query to fatch the data but both the filed name is same MENGE so wrong result display. then how can i display both the value in different filed.i also used modify statement but it will give me a dump .

my query is like:

SELECT A~BUKRS A~BSTYP A~BSART A~AEDAT
            A~LIFNR A~ZTERM A~EKORG A~EKGRP
            A~WAERS A~WKURS A~BEDAT A~EBELN

            B~EBELP B~LOEKZ B~MATNR B~WERKS
            B~LGORT B~BEDNR B~MATKL B~MENGE
            B~MEINS B~NETPR B~PEINH B~NETWR
            B~NAVNW B~EFFWR B~MWSKZ B~PSTYP
            B~KNTTP B~BANFN

            c~EBELN c~ETENR c~EINDT c~SLFDT
            c~menge c~BNFPO c~WEMNG
            FROM EKKO AS A
           INNER JOIN EKPO AS B ON A~EBELN = B~EBELN
           inner join EKET AS C ON B~EBELN = C~EBELN
           INTO CORRESPONDING FIELDS OF TABLE IT_EKKO WHERE A~BUKRS IN S_BUKRS AND A~EKORG IN S_EKORG
                                                      AND A~EBELN IN S_EBELN AND A~BSART IN S_BSART
                                                      AND A~BSTYP IN S_BSTYP AND A~LIFNR IN S_LIFNR
                                                      AND B~WERKS IN S_WERKS AND B~BANFN IN S_BANFN
                                                      AND B~MATNR IN S_MATNR AND B~MATKL IN S_MATKL
                                                      AND B~PSTYP IN S_PSTYP AND B~KNTTP IN S_KNTTP
                                                      AND B~LOEKZ IN S_LOEKZ

.

1 ACCEPTED SOLUTION

Former Member
0 Kudos
586

Hi,

Write inner join only on two tables and write for all entries on the remaining table.

SELECT A~BUKRS A~BSTYP A~BSART A~AEDAT
            A~LIFNR A~ZTERM A~EKORG A~EKGRP
            A~WAERS A~WKURS A~BEDAT A~EBELN
 
            B~EBELP B~LOEKZ B~MATNR B~WERKS
            B~LGORT B~BEDNR B~MATKL B~MENGE
            B~MEINS B~NETPR B~PEINH B~NETWR
            B~NAVNW B~EFFWR B~MWSKZ B~PSTYP
            B~KNTTP B~BANFN
 
            FROM EKKO AS A
           INNER JOIN EKPO AS B ON A~EBELN = B~EBELN
           INTO CORRESPONDING FIELDS OF TABLE IT_EKKO WHERE A~BUKRS IN S_BUKRS AND A~EKORG IN S_EKORG
                                                      AND A~EBELN IN S_EBELN AND A~BSART IN S_BSART
                                                      AND A~BSTYP IN S_BSTYP AND A~LIFNR IN S_LIFNR
                                                      AND B~WERKS IN S_WERKS AND B~BANFN IN S_BANFN
                                                      AND B~MATNR IN S_MATNR AND B~MATKL IN S_MATKL
                                                      AND B~PSTYP IN S_PSTYP AND B~KNTTP IN S_KNTTP
                                                      AND B~LOEKZ IN S_LOEKZ.

write select on EKET for all entries on the above internal table and get the entries.

Now you'll get both the MENGE values.

Regards,

Srini.

17 REPLIES 17

Former Member
0 Kudos
586

Whats the need of using join for 3 tables EKKO , EKPO and EKET. This may lead to performance issue too.

Use FOR ALL ENTRIES instead of Joins.

The result would be as simple.

Fetch PO Qty (MENGE) and Scheduled Qty (MENGE) as per the logic and pass it to different variables and move to final output table.

I would suggest you to remove joins and use FOR ALL ENTRIES instead.

0 Kudos
586

i also use for all entry but i want to display po line item as well as scheduling line item so using for all entry it will not display and in for all enters how can i put selection screen where condition becus it will not work. my query is like:

SELECT BUKRS BSTYP BSART AEDAT
            LIFNR ZTERM EKORG EKGRP
            WAERS WKURS BEDAT EBELN from ekko into table it_ekko where BUKRS IN S_BUKRS 
                                                                  AND EKORG IN S_EKORG
                                                                  AND EBELN IN S_EBELN 
                                                                  AND BSART IN S_BSART
                                                                  AND BSTYP IN S_BSTYP 
                                                                  AND LIFNR IN S_LIFNR.

     select EBELP lOEKZ MATNR WERKS
            LGORT BEDNR MATKL MENGE
            MEINS NETPR PEINH NETWR
            NAVNW EFFWR MWSKZ PSTYP
            KNTTP BANFN from ekpo INTO CORRESPONDING FIELDS OF TABLE IT_EKPO FOR ALL ENTRIES IN IT_EKKO 
                                                      WHERE EBELN = IT_EKKO-EBELN
                                                     AND WERKS IN S_WERKS 
                                                     AND BANFN IN S_BANFN
                                                      AND MATNR IN S_MATNR 
                                                      AND MATKL IN S_MATKL.

former_member1125862
Participant
0 Kudos
586

Hi,

Whats the use of the INNER JOINS.

Lets go with FOR ALL ENTRIES.

There must be performance issue while using the INNER JOINS.

Kruthik

Former Member
0 Kudos
587

Hi,

Write inner join only on two tables and write for all entries on the remaining table.

SELECT A~BUKRS A~BSTYP A~BSART A~AEDAT
            A~LIFNR A~ZTERM A~EKORG A~EKGRP
            A~WAERS A~WKURS A~BEDAT A~EBELN
 
            B~EBELP B~LOEKZ B~MATNR B~WERKS
            B~LGORT B~BEDNR B~MATKL B~MENGE
            B~MEINS B~NETPR B~PEINH B~NETWR
            B~NAVNW B~EFFWR B~MWSKZ B~PSTYP
            B~KNTTP B~BANFN
 
            FROM EKKO AS A
           INNER JOIN EKPO AS B ON A~EBELN = B~EBELN
           INTO CORRESPONDING FIELDS OF TABLE IT_EKKO WHERE A~BUKRS IN S_BUKRS AND A~EKORG IN S_EKORG
                                                      AND A~EBELN IN S_EBELN AND A~BSART IN S_BSART
                                                      AND A~BSTYP IN S_BSTYP AND A~LIFNR IN S_LIFNR
                                                      AND B~WERKS IN S_WERKS AND B~BANFN IN S_BANFN
                                                      AND B~MATNR IN S_MATNR AND B~MATKL IN S_MATKL
                                                      AND B~PSTYP IN S_PSTYP AND B~KNTTP IN S_KNTTP
                                                      AND B~LOEKZ IN S_LOEKZ.

write select on EKET for all entries on the above internal table and get the entries.

Now you'll get both the MENGE values.

Regards,

Srini.

0 Kudos
586

ya thats true but in i want to display po line item as well as scheduling line item so in which table i can put my loop if i put loop at it_ekko then po line item display but scheduling line item will not display and if i put loop at it_eket then scheduling line item display po line item will not display.

0 Kudos
586

Loop your haeder table and using READ statement fetch the values and move it to your final internal table.

Why to use loop's everywhere?

0 Kudos
586

ya i put loop at header table like but i will display only po line item not scheduling line item

l

oop at it_ekko.

      READ TABLE IT_EKET WITH KEY EBELN = IT_EKKO-EBELN .
      READ TABLE IT_EBAN WITH KEY BANFN = IT_EKko-BANFN .
      READ TABLE IT_EKKN WITH KEY EBELN = IT_EKko-EBELN .

      WA-BUKRS = IT_EKKO-BUKRS. "  Company Code
      WA-BSTYP = IT_EKKO-BSTYP. "  Purchasing Document Category
      WA-BSART = IT_EKKO-BSART. "  Purchasing Document Type
      WA-AEDAT = IT_EKKO-AEDAT. "  Date on Which Record Was Created
      WA-LIFNR = IT_EKKO-LIFNR. "  Vendor Account Number
      WA-ZTERM = IT_EKKO-ZTERM. "  Terms of Payment Key
      WA-EKORG = IT_EKKO-EKORG. "  Purchasing Organization
      WA-EKGRP = IT_EKKO-EKGRP. "  Purchasing Group
      WA-WAERS = IT_EKKO-WAERS. "  Currency Key
      WA-WKURS = IT_EKKO-WKURS. "  Exchange Rate
      WA-BEDAT = IT_EKKO-BEDAT. "  Purchasing Document Date
      WA-PSPNR = IT_EKKN-PSPNR. "  Project No.

      WA-EBELN = IT_EKKO-EBELN. "  Purchasing Document Number
      WA-EBELP = IT_EKKO-EBELP. "  Item Number of Purchasing Document
      WA-LOEKZ = IT_EKKO-LOEKZ. "  Deletion Indicator in Purchasing Document
      WA-MATNR = IT_EKKO-MATNR. "  Material Number
      WA-WERKS = IT_EKKO-WERKS. "  Plant
      WA-LGORT = IT_EKKO-LGORT. "  Storage Location
      WA-BEDNR = IT_EKKO-BEDNR. "  Requirement Tracking Number
      WA-MATKL = IT_EKKO-MATKL. "  Material Group
      WA-MENGE = IT_EKKO-MENGE. "  Purchase Order Quantity

      WA-MEINS = IT_EKKO-MEINS. "  Purchase Order Unit of Measure
      WA-NETPR = IT_EKKO-NETPR. "  Net Price in Purchasing Document (in Document Currency)
      WA-PEINH = IT_EKKO-PEINH. "  Price Unit
      WA-NETWR = IT_EKKO-NETWR. "  Net Order Value in PO Currency
      WA-NAVNW = IT_EKKO-NAVNW. "  Non-deductible input tax
      WA-EFFWR = IT_EKKO-EFFWR. "  Effective value of item
      WA-MWSKZ = IT_EKKO-MWSKZ. "  Sales Tax Code
      WA-PSTYP = IT_EKKO-PSTYP. "  Item Category in Purchasing Document
      WA-KNTTP = IT_EKKO-KNTTP. "  Account Assignment Category
      WA-BANFN = IT_EKKO-BANFN. "  Purchase Requisition Number

      WA-EINDT = IT_EKKO-EINDT. "  Item Delivery Date
      WA-SLFDT = IT_EKKO-SLFDT. "  Statistics-Relevant Delivery Date
      WA-MENGE1 = IT_EKKO-MENGE1. "  Scheduled Quantity
      WA-BNFPO = IT_EKKO-BNFPO. "  Item Number of Purchase Requisition
      WA-WEMNG = IT_EKKO-WEMNG. "  Quantity of Goods Received

      WA-BADAT = IT_EBAN-BADAT. "  PR Created
      WA-LFDAT = IT_EBAN-LFDAT. "  PR Req. Date

      WA-PS_PSP_PNR = IT_EKKN-PS_PSP_PNR. "  WBS Element
      WA-NPLNR = IT_EKKN-NPLNR. "  Network

        APPEND WA TO ITAB.
        CLEAR WA.
        CLEAR: IT_EKKO, IT_EKKN, IT_EBAN.

0 Kudos
586

Loop your haeder table and using READ statement fetch the values and move it to your final internal table.

Why to use loop's everywhere?

You can go this way.

  LOOP AT it_ekko INTO wa_ekko.
            MOVE : wa_ekko-ebeln TO wa_output-ebeln,
                         wa_ekko-menge TO wa_output-menge.
             READ TABLE it_eket INTO wa_eket
                       WITH KEY ebeln = wa_ekko-ebeln.
                IF sy-subrc = 0.
                    MOVE : wa_eket-menge TO wa_output-menge.
               ENDIF.
         ENDLOOP

0 Kudos
586

This isn't the way you have coded.

 loop at it_ekko into wa_ekko.
 
      READ TABLE IT_EKET WITH KEY EBELN = IT_EKKO-EBELN .
      -->> then move the fields to be fetched for EKET.

      READ TABLE IT_EBAN WITH KEY BANFN = IT_EKko-BANFN .
     -->> then move the fields to be fetched for EBAN
.
      READ TABLE IT_EKKN WITH KEY EBELN = IT_EKko-EBELN .
     -->> then move the fields to be fetched for EKKN

0 Kudos
586

i think i write a same thing but it will display only po line item it will not display scheduling line item

0 Kudos
586

Why so?

You have both the quantities picked from different tables. Right?

Store them in their respective variables and show them in output.

I don't think its a big deal...Where is your problem? Be specific

0 Kudos
586

My problem is for example:

my PO number is 00001

and in same po line item is

MATNR 00011 or 00022 ok in 00011 material there r two scheduling line item and for second material there r 3 line item so

when first time loop will execute at that time MATNR is for example

material no 00011 fetch and scheduling line 1 fetch ok

but second time loop execute MATNR gets changed 00022 so for the first material second scheduling line item not fetch

0 Kudos
586

Use loop inside loop with where condition.

Regards,

Thiru

0 Kudos
586

Hi ,

Use the Parallel processing method.

i.e Loop inside a loop with sy-tabix.

example.

Loop at itab1 into wa_itab1.

read itab2 into wa_itab2 with key with itab1.

if sy-subrc = 0.

take the sy-tabix into one variable.

then

loop at itab2 into wa_itab2 from v_tabix.

if wa_itab2-matnr <> wa_itab1-matnr.

exit.

endif.

endloop.

Thanks and Regards,

Bharani

0 Kudos
586

Everything will work fine. Use Nested Loops. Try to debug and play with your code, you can find the solution of your own.

0 Kudos
586

ya thanks it will work but now prob is if 1 material have only one scheduling line item and 2 material have 3 line item then it will display 4 line item for both the material.

0 Kudos
586

thanks a lot my prob get solved.