Joining AFIH and OBJK table for PM order

[Former Member]

Hi Experts,

I need to join the Table AFIH and OBJK  and I have joining condition

AFIH-OBKNR = OBJK-OBKNR. the reason I need to join this two table because the user will maintain the equipment number in AFIH-EQUNR for single equipment but for multiple equipment they will maintain the equipment in object list where it will go to base table of OBJK-OBKNR.  but the condition for multiple equipment number the object list from both table will be have some values so

READ TABLE IT_AFIH INTO WA_AFIH WITH KEY AUFNR = WA_AUFK-AUFNR BINARY SEARCH.



      IF SY-SUBRC = 0.





        WA_ZSCEOVERVIEW-PRIOK      = WA_AFIH-PRIOK.

        WA_ZSCEOVERVIEW-OBKNR      = WA_AFIH-OBKNR.

        WA_ZSCEOVERVIEW-EQUNR      = WA_AFIH-EQUNR.



      ENDIF.

*





READ TABLE IT_OBJK INTO WA_OBJK WITH KEY OBJVW = 'A' BINARY SEARCH.



      IF SY-SUBRC = 0.



        IF WA_OBJK-OBKNR IS NOT INITIAL.



          WA_ZSCEOVERVIEW-ZOBJEK_OBKNR = WA_OBJK-OBKNR.

          WA_ZSCEOVERVIEW-OBZAE        = WA_OBJK-OBZAE.

          WA_ZSCEOVERVIEW-OBJVW        = WA_OBJK-OBJVW.

          WA_ZSCEOVERVIEW-ZOBJEK_EQUNR = WA_OBJK-EQUNR.



        ENDIF.

      ENDIF.



   IF WA_ZSCEOVERVIEW-EQUNR  IS NOT INITIAL.

    MOVE WA_ZSCEOVERVIEW-EQUNR TO  WA_ZSCEOVERVIEW-ZZEQUIPMENT.



      ELSEIF WA_ZSCEOVERVIEW-ZOBJEK_EQUNR IS NOT INITIAL.



         MOVE WA_ZSCEOVERVIEW-ZOBJEK_EQUNR TO  WA_ZSCEOVERVIEW-ZZEQUIPMENT.

      ENDIF.

but I am getting value only from AFIH equipment number but not from OBJK.  hence kindly let me know what went wrong please.

[Former Member]

Hi Rajeshvaramana,

Why don´t you try an inner join instead?

Regards,

Christian

[Former Member]

Hi Christian,

Thanks for your prompt action, I guess we can't use inner join for my case. because if equipment number is not initial in AFIH table means I need to read equipment number from AFIH or else the equipment number is blank in AFIH and Object list is having some valuse means I need to get equipment number from OBJK table so how I can we do inner join.

[Former Member]

Hi Rajeshvaramana,

In this case you can try a LEFT JOIN.

Example:

REPORT zr_example.

TABLES: afih.

SELECT-OPTIONS: s_aufnr FOR afih-aufnr.

TYPES: BEGIN OF local_pm_orders,

          aufnr  TYPE afih-aufnr,

          equnr  TYPE afih-equnr,

          obknr  TYPE objk-obknr,

          obzae2  TYPE objk-obzae,

          equnr2 TYPE objk-equnr,

        END OF local_pm_orders.

DATA: t_pm_orders TYPE STANDARD TABLE OF local_pm_orders.

SELECT a~aufnr a~equnr b~obknr b~obzae b~equnr AS equnr2

   INTO CORRESPONDING FIELDS OF TABLE t_pm_orders

   UP TO 100000 ROWS

   FROM afih AS a

   LEFT JOIN objk AS b

   ON a~obknr = b~obknr

   WHERE aufnr IN s_aufnr.

Reference

http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

Regards,

Christian

[Former Member]

here:

READ TABLE IT_OBJK INTO WA_OBJK WITH KEY OBJVW = 'A' BINARY SEARCH.

you don't check the objektlist no from AFIH, it should looks like:

READ TABLE IT_OBJK INTO WA_OBJK WITH KEY OBJVW = 'A'

                                                                                     OBKNR = WA_AFIH-OBKNR

                                                                                     BINARY SEARCH.

Regrads Hendrik