Reg.Select Statement ?

[former_member194669]

Hi,

I have two tables MARA and YMARA. I need to select MATNR and MTART fields from these two tables and move the values to a internal table. (Here YMARA is copy of MARA table).

I have to use a single select statement and get the values from these two tables. my where condition will MATNR = 'ABC'.

For some technical reason i could not able to make two select statements and move the values from these tables into a internal table. and i don't have any database view also for this.

aRs

[Former Member]

So, use a JOIN.

SELECT mara~matnr ymara-matnr ....

into table itab

from mara join ymara on

mara_{matnr = ymara}matnr

where ...

Rob

Message was edited by:

Rob Burbank

[Former Member]

Or make your own view.

Rob

[Former Member]

Write a join for these two table MARA and YMARA on A_{MATNR = B}MATNR.

selct single a~MTART as MTART1

b~MTART as MTART2 into table ITAB

from MARA as a inner join YMARA as B ON (A_{MATNR = B}MATNR)

where a~matnr = 'ABC'.

- Raj

Message was edited by:

Rajanya Kolavennu

[former_member187255]

try this...

SELECT A_{matnr A}mtart INTO TABLE ITAB

FROM mara AS A INNER JOIN ymara AS B

ON A_{matnr = B}matnr

where A~matnr = 'ABC'.

[Former Member]

Hi

In the internal table declare 4 fields now use Join Statement,

select a_{matnr a}mtart b_{matnr b}mtart into table itab from mara as a innerjoin ymara as b on a_{matnr = b}matnr

where a~matnr = 'ABC'.

Regards

Haritha.

[Former Member]

Hello,

I understand that you want to do something like

SELECT MATNR MTART FROM MARA INTO TABLE ... WHERE MATNR = 'ABC'.

SELECT MATNR MTART FROM YMARA APPENDING TABLE ... WHERE MATNR = 'ABC'.

I actually doubt that you can achieve this with a single command.

Could you elaborate on the "technical reason" why you cannot write 2 statements?

[former_member194669]

Thanks all for your replies.

I think i have not given my question properly.

After the select i need to records comes this way.

MATNR  MTART
ABC       01                         " <--MARA record
ABC       02                         " <--YMARA record 
ABC       03                         " <--YMARA record
ABC       04                         " <--YMARA record

For performance reason i could not able to create a database view., both these tables contains 13 million records each.

Technical reason for not to make 2 select statement

is this particular select statement is called from

a SAP C+ program.

aRs

[Former Member]

Then try an outer join:

SELECT mara~matnr ymara-matnr ....
into table itab
from mara left outer join ymara on
mara~matnr = ymara~matnr
where mara~matnr = ...
     or ymara~matnr = ...

Rob

[former_member194669]

Any suggestions please?

Thanks

aRs.

[former_member194669]

Thanks Rob,

If we make a left outer join from MARA to YMARA. If MARA does not contains a record but YMARA does have then that record will not get fetch?

Thanks

aRs

[RichHeilman]

That's right, if there is no record in the table on the left, then no record will be retrieved for the table on the right. If you have this type of sernario where either table may or may not have a match to the other, then you really have no choice but to go with 2 separate select statements, APPENDING to the internal table.

Regards,

Rich Heilman

[Former Member]

Hi

Use the following select statement,

Select matnr from mara into table itab1.

select matnr mtart from ymara into table itab2 for all entries in itab1 where matnr = itab1-matnr.

write:/ itab2-matnr, itab2-mtart.

Reward me if its useful.

Regards

ravi

[Former Member]

Hello again,

MARA's primary key is MATNR, so SELECT MATNR MTART FROM MARA INTO... WHERE MATNR EQ 'ABC' is the most efficient lookup even if the table has 13 million entries. The same for YMARA, if YMARA's primary key is also only (MANDT and) MATNR.

It's not possible to use a JOIN or database view for your purposes, since you need a UNION of the tables, while a Join/DB view only gives you the cross product.

However, I'm still strongly of the opinion that it is NOT possible to do what you want with 1 statement. I Don't know how the C+ access to SAP Open SQL works, but are the following possiblities alternatives you may use?

1)

SELECT matnr mtart FROM: MARA INTO TABLE... WHERE matnr EQ 'ABC', YMARA APPENDING TABLE... WHERE matnr EQ 'ABC'.

These are technically two commands, but you have only one dot

2)

Encapsulate these two commands in one function module/method/subprogram and call that function from you C+ program.

3)

Dispatch 2 select calls from your C+ program: One to select entries from MARA, one to add entries from YMARA.

Regards,

Björn

[former_member194669]

Björn

I used your 2 point

Encapsulate these two commands in one function module/method/subprogram and call that function from you C+ program.

Thanks

aRs