SQL problem

[Former Member]

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.

[Former Member]

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.

[athavanraja]

SELECT r_{matnr c}werks

INTO CORRESPONDING FIELDS OF TABLE itab

FROM mara AS r

INNER JOIN marc AS c ON r_{matnr = c}matnr .

Regards

Raja

[jayanthi_jayaraman]

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 mara_{matnr marc}werks into table itab from mara inner join marc on mara_{matnr = marc}matnr.

[Vinod_Chandran]

Try this

select a_{matnr b}werks into table i_mara

from mara as a inner join marc as b

on a_{matnr = b}matnr

where a~matnr = << Your condition goes here

[andreas_mann3]

Hi,

... or take a view like ma06v

SELECT matnr werks ...

INTO CORRESPONDING FIELDS OF itab

FROM ma06v

WHERE werks = werks.

....

regards Andreas

[Former Member]

Hi,

Must I use inner join ?

[jayanthi_jayaraman]

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

[athavanraja]

YES

[Former Member]

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

[jayanthi_jayaraman]

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

[Former Member]

Hi,

Where is WERKS?

I want to select it into itab too.

[jayanthi_jayaraman]

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]

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