topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Fri, 22 Dec 2006 19:01:55 GMT

Hi all,

I need to inner join two tables A B on material number.

Table A has three fields:

MaterialNum Description Type

1 A I

2 B I

3 C I

Table B has two fields:

MaterialNum UOM

1 EA

1 PA

The result I want is

MaterialNum Description UOM

1 A EA

1 A PA

The code I wrote is:

DATA: BEGIN OF I_ITABOCCURS 0,

MANUM TYPE ***,

DESC TYPE ***,

UOM TYPE ***,

END OF ITEM_DITAB.

SELECT a_{MANUM a}DESC b~UOM

INTO CORRESPONDING FIELDS OF TABLE I_ITAB

FROM B AS b

INNER JOIN A AS a

ON a_{MANUM = b}MANUM

WHERE a~TYPE = 'I'

The result I got was only one line:

1 A EA

Can you give me some advise? Thanks!

Linda

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:09:45 GMT

User left inner join and try.

Thanks,

Santosh

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:14:35 GMT

thanks for reply.

I tried left inner join, but I got sydex error.

I wrote this code in BW system. Would that be the problem? I do not think so, but...

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:16:05 GMT

Hi,

Did you check if the table B has both the records..The sql seems to look fine..

Thanks,

Naren

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:19:56 GMT

I check the table from SE16. I can see those records.

I just tried the code

select * from B

into corresponding fields of table I_ITAB.

I only got one record back too.

Then why I can see those records from SE16?

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:27:32 GMT

Hi,

Even if you do

SELECT * FROM TABLE B

INTO CORRESPONDING FIELDS OF TABLE I_ITAB.

You are getting only one record....

I am not sure why???

Check if you doing SE16 and program execution in the same client..

Meaning..You might be running the program in development client and doing the SE16 in testing client..

Thanks,

Naren

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:34:42 GMT

I am very sure I am doing those two things on the same client. Any idea?

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:39:14 GMT

Why dont you try without WHERE clause and check?

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:40:41 GMT

My mistake,

I got two records back when I use Select * from B.....

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:50:35 GMT

Hi,

Check this example ...

PARAMETERS: P_MATNR TYPE MATNR.

DATA: BEGIN OF ITAB OCCURS 0,

MATNR TYPE MATNR,

MAKTX TYPE MAKTX,

END OF ITAB.

SELECT B_{MATNR B}MAKTX

INTO TABLE ITAB

FROM MARA AS A INNER JOIN MAKT AS B

ON A_{MATNR = B}MATNR

WHERE A~MATNR = P_MATNR.

WRITE: / SY-DBCNT.

Thanks,

Naren

Re: Inner join

Former Member — Fri, 22 Dec 2006 19:53:13 GMT

HI,

Can you try this, removing the corresponding fields of...

SELECT a_{MANUM a}DESC b~UOM

INTO TABLE I_ITAB

FROM B AS b

INNER JOIN A AS a

ON a_{MANUM = b}MANUM

WHERE a~TYPE = 'I'.

Thanks,

-Venkat.

Re: Inner join

Former Member — Fri, 22 Dec 2006 20:15:34 GMT

THANKS A LOT.

it is the problem in where clause.

Linda