topic Re: SQL problem in Application Development and Automation Discussions

SQL problem

Former Member — Wed, 20 Jul 2005 06:44:48 GMT

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.

Re: SQL problem

athavanraja — Wed, 20 Jul 2005 06:51:57 GMT

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

Re: SQL problem

Vinod_Chandran — Wed, 20 Jul 2005 06:53:55 GMT

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

Re: SQL problem

andreas_mann3 — Wed, 20 Jul 2005 06:58:14 GMT

Hi,

... or take a view like ma06v

SELECT matnr werks ...

INTO CORRESPONDING FIELDS OF itab

FROM ma06v

WHERE werks = werks.

....

regards Andreas

Re: SQL problem

Former Member — Wed, 20 Jul 2005 06:59:57 GMT

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

Re: SQL problem

jayanthi_jayaraman — Wed, 20 Jul 2005 07:00:13 GMT

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.

Re: SQL problem

Former Member — Wed, 20 Jul 2005 07:01:15 GMT

Hi,

Must I use inner join ?

Re: SQL problem

jayanthi_jayaraman — Wed, 20 Jul 2005 07:08:08 GMT

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

Re: SQL problem

athavanraja — Wed, 20 Jul 2005 07:10:11 GMT

YES

Re: SQL problem

Former Member — Wed, 20 Jul 2005 07:15:41 GMT

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

Re: SQL problem

jayanthi_jayaraman — Wed, 20 Jul 2005 08:32:32 GMT

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

Re: SQL problem

Former Member — Wed, 20 Jul 2005 08:57:49 GMT

Hi,

Where is WERKS?

I want to select it into itab too.

Re: SQL problem

jayanthi_jayaraman — Wed, 20 Jul 2005 09:08:04 GMT

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?