Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL problem

Former Member
0 Kudos

Hi all,

I'd like to use one sql to select MATNR from MARA and WERKS from MARC.

select MATNR MARC~WERKS

into corresponding fields of itab

from MARA

where exists ( select * from MARC where MATNR = MARA~MATNR ).

But I got a syntax error saying that wrong table name for MARC. How should I correct the SQL ?

Thanks.

12 REPLIES 12

athavanraja
Active Contributor
0 Kudos

SELECT rmatnr cwerks

INTO CORRESPONDING FIELDS OF TABLE itab

FROM mara AS r

INNER JOIN marc AS c ON rmatnr = cmatnr .

Regards

Raja

0 Kudos

Hi,

If you don't want to use alis name,

you can code like this.

types : begin of ty,

matnr type mara-matnr,

werks type marc-werks,

end of ty.

data itab type standard table of ty.

select maramatnr marcwerks into table itab from mara inner join marc on maramatnr = marcmatnr.

Vinod_Chandran
Active Contributor
0 Kudos

Try this

select amatnr bwerks into table i_mara

from mara as a inner join marc as b

on amatnr = bmatnr

where a~matnr = << Your condition goes here

0 Kudos

Hi,

... or take a view like ma06v

SELECT matnr werks ...

INTO CORRESPONDING FIELDS OF itab

FROM ma06v

WHERE werks = werks.

....

regards Andreas

0 Kudos

Hi,

Must I use inner join ?

0 Kudos

Hi,

You can use for all entries also.

Here itab is a internal table containing matnr and itab1 is a internal table containing both matnr and werks.

select matnr from mara into table itab.

select matnr werks from marc into table itab1 for all entries in itab where matnr = itab-matnr.

Check your question on select-option.I found new solution.Check and let me know whether that is useful.

Message was edited by: Jayanthi Jayaraman

0 Kudos

YES

0 Kudos

Hi,

Must I start with MARC first?

my actual sql is:

I want to select WERKS into itab too

SELECT MATNR SPART LABOR

INTO CORRESPONDING FIELDS OF ITAB

FROM MARA

WHERE MATNR IN S_MATNR

AND SPART IN S_SPART

AND LABOR IN S_LABOR

AND MTART = 'FERT'

AND LVORM = ''

AND MSTAE = ''

AND MATNR IN ( SELECT MATNR FROM MVKE WHERE VKORG = '3000' AND VTWEG = '03' AND LVORM = '' )

AND NOT EXISTS ( SELECT * FROM MARC WHERE MATNR = MARA~MATNR AND WERKS IN S_WERKS ).

0 Kudos

Hi,

Try this.

SELECT MATNR SPART LABOR

INTO CORRESPONDING FIELDS OF <b>table</b> ITAB

FROM MARA

WHERE MATNR IN ( SELECT MATNR FROM MVKE WHERE VKORG = '3000' AND VTWEG =

'03' AND LVORM = '' )

AND <b>matnr NOT in</b> ( SELECT * FROM MARC WHERE MATNR = MARA~MATNR AND WERKS IN S_WERKS )

and MATNR IN S_MATNR

AND SPART IN S_SPART

*AND LABOR IN S_LABOR

AND MTART = 'FERT'

AND LVORM = ''

AND MSTAE = ''.

0 Kudos

Hi,

Where is WERKS?

I want to select it into itab too.

0 Kudos

Hi,

There is no werks in MARA and MVKE.You want the Matnr output should not be in MARC.Then how is it possible to get the corresponding WERKS for MATNR from MARC?

Former Member
0 Kudos

Hi,

Instead of Nested select statement you can use Inner joins to achieve your purpose.

Anyway.... check the below SQL...

select MATNR WERKS

into corresponding fields of table it_output

from MARC as a

where exists ( select * from MARA as b where MATNR = a~MATNR ).

endselect.

Regards,

Vara