topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Thu, 02 Jun 2005 19:54:15 GMT

Hi All,

I am using Inner Join on two tables PA0001 and PA0006 on the field PERNR. But I am getting nothing as output.

The code is as follows:

TABLES: PA0001, PA0006.

INFOTYPES: 0001, 0006.

DATA: BEGIN OF TEST_TBL OCCURS 100,

P_NAME LIKE P0001-UNAME,

P_STREET LIKE P0006-STRAS,

P_CITY LIKE P0006-ORT01,

P_ZIP LIKE P0006-PSTLZ,

END OF TEST_TBL.

SELECT PA0001_{UNAME PA0006}STRAS PA0006_{ORT01 PA0006}PSTLZ

INTO CORRESPONDING FIELDS OF TABLE TEST_TBL

FROM ( PA0001 INNER JOIN PA0006 ON PA0001_{PERNR = PA0006}PERNR )

WHERE PA0006~STATE <> 'FL'.

LOOP AT TEST_TBL.

WRITE: /, TEST_TBL-P_NAME.

ENDLOOP.

I dont know whether there is some logical error or not.

Thanks in advance,

Gaurav

Re: Inner Join

Former Member — Thu, 02 Jun 2005 20:24:24 GMT

Hi,

Did you check whether PA0001 and PA0006 has data.

Thanks.

Re: Inner Join

Former Member — Thu, 02 Jun 2005 20:30:10 GMT

Yes both PA0001 and PA0006 have data. In both case there are many entries for which PERNR match.

Gaurav

Re: Inner Join

Former Member — Thu, 02 Jun 2005 20:54:52 GMT

Hi,

Why do you have those parantheses in the code. Does that make any difference. Try removing those and see if that makes any diference. Also check the other where condition (for 'FL').

Thanks.

Re: Inner Join

Former Member — Thu, 02 Jun 2005 21:41:25 GMT

Hi Gaurav,

The problem is with the names in your internal table.

When you use INTO CORRESPONDING FIELDS OF, it tries to match the field names of the internal table to the fields being selected from the tables. In your case the internal table fields P_NAME, P_STREET, P_CITY, and P_ZIP doe not match the fields from the tables UNAME, STRAS, ORT01, PSTLZ.

So you can fix this by renaming the fields in your internal table, or my preferred way would be to use the AS clause in the SELECT statement. This allows you to specify your own field names in the select.

So you could do your select statement like this:


SELECT PA0001~UNAME AS P_NAME PA0006~STRAS AS P_STREET PA0006~ORT01 AS P_CITY PA0006~PSTLZ AS P_ZIP
INTO CORRESPONDING FIELDS OF TABLE TEST_TBL
FROM ( PA0001 INNER JOIN PA0006 ON PA0001~PERNR = PA0006~PERNR )
WHERE PA0006~STATE <> 'FL'.

This should fix your problem.

Cheers,

Brad

Re: Inner Join

Former Member — Thu, 02 Jun 2005 21:42:06 GMT

Also, another solution in your case (although not very elegant) is that, because the structure of the fields in your select statement, and the structure of the fields in the internal table are identical (same number of fields, all fields same type, fields in same order) you could just use: INTO TABLE TEST_TBL rather than INTO CORRESPONDING FIELDS OF TABLE TEST_TBL.

Any of the 3 options I presented should fix your problem.

Brad

Re: Inner Join

Former Member — Fri, 03 Jun 2005 13:18:04 GMT

Thanks for the suggestions. I incorporated the AS clause in the Select statement and its working well since then.

Can you tell what should I prefer if I am extracting data from more than three tables; Join or Provide clause.

Thanks,

Gaurav

Re: Inner Join

Former Member — Fri, 03 Jun 2005 13:40:51 GMT

Thanks Gaurav,

Not sure that the PROVIDE clause is what you are after.

I would stick with JOIN, or else create a view in SE11.

Cheers,

Brad

Re: Inner Join

Former Member — Fri, 03 Jun 2005 13:56:56 GMT

Actually the thing is that I want to extract the values which lie between two dates entered by the user at the run time. For the users to enter the dates, I am using the Select-Options clause.The code is like this...

SELECT-OPTIONS MY_DATE FOR SY-DATUM DEFAULT '19980101' TO '19981231'.

TABLES: PA0001, PA0006, PA0008.

DATA: BEGIN OF TEST_TBL OCCURS 100,

P_NAME LIKE P0001-UNAME,

P_SDATE LIKE P0006-BEGDA,

P_EDATE LIKE P0006-ENDDA,

P_STREET LIKE P0006-STRAS,

P_CITY LIKE P0006-ORT01,

P_ZIP LIKE P0006-PSTLZ,

P_WAGE_TYPE LIKE P0008-LGA01,

END OF TEST_TBL.

SELECT PA0001_{UNAME AS P_NAME PA0006}STRAS AS P_STREET PA0006_{ORT01 AS P_CITY PA0006}PSTLZ AS P_ZIP PA0008~LGA01 AS P_WAGE_TYPE

INTO CORRESPONDING FIELDS OF TABLE TEST_TBL

FROM ( PA0001 INNER JOIN PA0006 ON PA0001_{PERNR = PA0006}PERNR

INNER JOIN PA0008 ON PA0001_{PERNR = PA0008}PERNR )

WHERE MY_DATE BETWEEN PA0006_{BEGDA AND PA0006}ENDDA.

LOOP AT TEST_TBL.

WRITE: /, TEST_TBL.

ENDLOOP.

Since the dates are entered at runtime, the complier fails to recognize the variable MY_DATE and gives an error.

Thanks in advance,

Gaurav

Re: Inner Join

Former Member — Sun, 05 Jun 2005 14:27:55 GMT

Hi Guarav,

If your last post is still open I suggest you open a new thread.

Cheers,

Brad