inner join query

[Former Member]

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

[Jelena]

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.

[ChrisSolomon]

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 table....so you have to be specific....ie. NOT ambiguous....about which one you are referencing.

[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.

SELECTION-SCREEN begin of BLOCK a1 WITH FRAME TITLE text-001.

SELECT-OPTIONS v_order FOR zfurnace_dwn-AUFNR.

SELECTION-SCREEN end of BLOCK a1.

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.

ENDFORM.

[horst_keller]

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?

[ChrisSolomon]

Now I think you see why he did NOT want to post code before. haha

[raghug]

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.
  OR
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.

[raghug]

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!