topic Re: Problem in select query. in Application Development and Automation Discussions

Problem in select query.

Former Member — Wed, 19 Apr 2006 10:28:30 GMT

Hi ABAP gurus,

What is the problem with this query.

SELECT A~MATNR

A~MTART

B~werks

c~lgort

c~labst

INTO CORRESPONDING FIELDS OF IT_MAT

FROM mara AS A

left outer JOIN marc AS B ON a_{MATNR = B}MATNR

left outer join mard as c on b_{matnr = c}matnr and c_{werks = b}werks

WHERE a~MATNR IN S_MATNR .

ENDSELECT.

It is giving me an error saying that table can be joined with max of one other table using left outer join

My requirement involves joining of these three tables this way to pick all those material numbers

which are in the material range s_matnr and have all werks related from marc and all storage locations

related to werks.

How to do it?

Nik

Re: Problem in select query.

Former Member — Wed, 19 Apr 2006 10:30:54 GMT

Hi Nik,

Left outer join does not work on three tables like this.

But You can write left outer join as follows....

mara left outer join marc on mara-matnr = marc-matnr

mara left outer join mard on mara-matnr = mard-matnr

or what best you can do is to make use of views.

Try doing it with above mentioned.

Regards,

suruchi

Re: Problem in select query.

Former Member — Wed, 19 Apr 2006 10:35:29 GMT

GOOD

YOU CAN TRY YOUR LEFT OUTER JOIN LIKE THIS SYNTAX

SELECT...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond>

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid AND

p~cityfrom = 'FRANKFURT'.

LOOP AT itab INTO wa.

WRITE: / wa-carrid, wa-carrname, wa-connid.

ENDLOOP.

THANKS

MRUTYUN

Re: Problem in select query.

Former Member — Wed, 19 Apr 2006 10:37:47 GMT

Nikhil,

Try using a simple join between the view MAWEV and table MARD.

You can try the outer join as well.

Regards,

Ravi

Re: Problem in select query.

Former Member — Wed, 19 Apr 2006 10:40:30 GMT

Hi Nikhil,

Try writing the select query using the brackets.

Regards,

Sameena

Re: Problem in select query.

Former Member — Wed, 19 Apr 2006 10:41:23 GMT

Ya I could do it.

Thanx.

Nik