Inner join

[Former Member]

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

[Former Member]

User left inner join and try.

Thanks,

Santosh

[Former Member]

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

[Former Member]

Hi,

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

Thanks,

Naren

[Former Member]

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?

[Former Member]

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

[Former Member]

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

[Former Member]

Why dont you try without WHERE clause and check?

[Former Member]

My mistake,

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

[Former Member]

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.

[Former Member]

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

[Former Member]

THANKS A LOT.

it is the problem in where clause.

Linda