topic Re: Joining AFIH and OBJK table for PM order in Application Development and Automation Discussions

Joining AFIH and OBJK table for PM order

Former Member — Thu, 27 Dec 2012 06:45:24 GMT

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.

Re: Joining AFIH and OBJK table for PM order

Former Member — Thu, 27 Dec 2012 07:00:56 GMT

Hi Rajeshvaramana,

Why don´t you try an inner join instead?

Regards,

Christian

Re: Joining AFIH and OBJK table for PM order

Former Member — Thu, 27 Dec 2012 08:39:15 GMT

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.

Re: Joining AFIH and OBJK table for PM order

Former Member — Thu, 27 Dec 2012 11:00:37 GMT

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

Re: Joining AFIH and OBJK table for PM order

Former Member — Thu, 27 Dec 2012 12:15:57 GMT

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