Skip to Content
avatar image
Former Member

inner join query

Hi folks!

i have two tables zfurnace_dwn(table1) & zfurnace(table2).

data to be fetched from table1 : code (code id), aufnr (order no.)

data to be fetched from table2 : code_text (code description).

my o/p is like : aufnr code code text.

i apply inner join on 2 tables with code field being the key common field in both my ztables.

now my query is when i apply inner join on the above 2 tables in my 'select' statement i get an error in my program as field - code is ambigous .

i guess this is a very small issue but i just cannot figure it out .

pl help

Add comment
10|10000 characters needed characters exceeded

  • What is"o/p"? If you need assistance with specific ABAP code then just post the actual code. How are we supposed to guess what could be wrong with the code we do not see? General help can be also found in, well, ABAP Help.

  • o/p - simple, that is Out Patient. I liked those days because I could still sit in the reception and sneak into my parents offices between patients. I/P (In-patient) days, they were just gone!

  • Get RSS Feed

3 Answers

  • Jan 16, 2017 at 07:40 PM

    The field "code" is ambiguous.....because it does not know WHICH "code" you are talking about ....the one in the zfurnace_dwn table or the one in the zfurnace you have to be NOT ambiguous....about which one you are referencing.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      here is my below code .

      TABLES : zfurnace_dwn , zfurnace.

      TYPES : BEGIN OF ty_zfurnace1,
      AUFNR TYPE zfurnace_dwn-AUFNR, " Process Order No.
      ON_DATE TYPE zfurnace_dwn-ON_DATE, "Breakdown duration
      RMZHL TYPE zfurnace_dwn-RMZHL, "Confirmation Counter
      Code TYPE zfurnace_dwn-Code, "Breakdown Code
      END OF ty_zfurnace1.

      TYPES : BEGIN OF ty_zfurnace2,
      CODE TYPE zfurnace-CODE,
      CODE_TEXT TYPE zfurnace-CODE_TEXT,
      END OF ty_zfurnace2.


      SELECT-OPTIONS v_order FOR zfurnace_dwn-AUFNR.


      DATA : it_furnace1 TYPE STANDARD TABLE OF ty_zfurnace1,
      wa_furnace1 TYPE ty_zfurnace1,
      it_furnace2 TYPE STANDARD TABLE OF ty_zfurnace2,
      wa_furnace2 TYPE ty_zfurnace2.

      PERFORM get_data.
      *& Form GET_DATA
      * text
      * --> p1 text
      * <-- p2 text
      FORM get_data .

      *SELECT a~aufnr a~code FROM zfurnace_dwn as a INNER JOIN zfurnace as b on a~code = b~code INTO CORRESPONDING FIELDS OF TABLE it_furnace1 where code = wa_furnace1-code.


  • Jan 17, 2017 at 05:38 AM

    Please have a look into the Open SQL documentation (it might be a good idea to use the ABAP documentation at all ...).

    You miss the table specification on the LHS of the WHERE condition.

    Furthermore you must use the table aliases defined with AS consistently in the whole SELECT statement. You mix up aliases with names.

    And, why the ... are you using TABLES and other obsolete stuff?

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 17, 2017 at 04:19 PM

    Translating what Horst and Christopher are saying (and also your error message) - In you WHERE clause, you must specify which table the field code is in with respect to your search. So, the end of your select statement should read either

    WHERE a~code = wa_furnace1-code.
    WHERE b~code = wa_furnace1-code.

    Even though I have no idea of what these tables are for or what the intent of this report is, I have a couple of other suggestions.

    1) Add AUFNR to your selection, otherwise over time when your table grows, the report is going to be very ineffecient

    2) On the other hand, if you are trying to report on the frequency of a code occurring and this table is going to continue to grow, add something else to narrow down the search - like a date range

    3) If you truly want the frequency from the beginning each time - you should consider a data warehousing solution rather than live SAP.

    Add comment
    10|10000 characters needed characters exceeded