topic Reg.Select Statement ? in Application Development and Automation Discussions

Reg.Select Statement ?

former_member194669 — Mon, 11 Jun 2007 14:21:01 GMT

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

Re: Reg.Select Statement ?

Former Member — Mon, 11 Jun 2007 14:28:46 GMT

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

Re: Reg.Select Statement ?

Former Member — Mon, 11 Jun 2007 14:29:23 GMT

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

Re: Reg.Select Statement ?

former_member187255 — Mon, 11 Jun 2007 14:30:59 GMT

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

Re: Reg.Select Statement ?

Former Member — Mon, 11 Jun 2007 14:31:20 GMT

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.

Re: Reg.Select Statement ?

Former Member — Mon, 11 Jun 2007 14:36:31 GMT

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?

Re: Reg.Select Statement ?

Former Member — Mon, 11 Jun 2007 14:41:41 GMT

Or make your own view.

Rob

Re: Reg.Select Statement ?

former_member194669 — Mon, 11 Jun 2007 14:53:36 GMT

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

Re: Reg.Select Statement ?

former_member194669 — Mon, 11 Jun 2007 15:13:22 GMT

Any suggestions please?

Thanks

aRs.

Re: Reg.Select Statement ?

Former Member — Mon, 11 Jun 2007 15:20:19 GMT

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

Re: Reg.Select Statement ?

former_member194669 — Mon, 11 Jun 2007 15:24:19 GMT

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

Re: Reg.Select Statement ?

RichHeilman — Mon, 11 Jun 2007 15:29:18 GMT

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

Re: Reg.Select Statement ?

Former Member — Mon, 11 Jun 2007 15:32:37 GMT

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

Re: Reg.Select Statement ?

Former Member — Tue, 12 Jun 2007 05:47:05 GMT

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?

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

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

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

Regards,

Björn

Re: Reg.Select Statement ?

former_member194669 — Tue, 12 Jun 2007 05:52:19 GMT

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