Join on future criterias

[DG]

Hi

I have a join in MSEG where I want to include NAST based on the purchase order.

The criteria is that I only want one line MSEG and only one line in NAST it exists. Also the NAST line sould be the youngest line there exists.

In Oracle I could do this with a subselect but can I do something similary in OPEN SQL.

Daniel

[faisal_altaf2]

Hi Daniel

Sorry, didn't get your requirement, Please explain it bit more.

Thanks and Regards,

Faisal

[UweFetzer_se38]

Hi Daniel,

with the exception, that you cannot create the NAST~OBJKY within the join, you can try things like ORDER BY and UP TO 1 ROWS.

Bye, Uwe (@se38)

[DG]

What i wanted was sometling like

select mseg~mblnr nast~nacha
from mseg
left join nast on nast~objky = mseg~ebeln and nast~kappl = 'EF' and NAST~KSCHL= 'NEU'
order by nast~erdat 

But the limit will only return the first entry of the full table.

[Former Member]

wont an addition UPTO 1 rows do with a jooin of NAST with MSEG.. (nast left join with mSEG)

some thing like this..

DATA : lv_erdat type erdat.
DATA lv_mblnr TYPE mblnr.
select mseg~mblnr nast~erdat
INTO (lv_mblnr, lv_erdat)
from nast
left join mseg on nast~objky = mseg~ebeln
UP TO 1 ROWS
  WHERE kappl = 'EF'
  and KSCHL = 'NEU'
order by erdat.
ENDSELECT.

[UweFetzer_se38]

SELECT mseg~mblnr
       mseg~mjahr
       mseg~zeile
       mseg~ebeln
       mseg~ebelp
       nast~kschl
       nast~erdat
       nast~eruhr
  INTO TABLE gt_sel

  FROM mseg

  INNER JOIN nast
  ON  nast~kappl = 'EF'
  AND nast~objky = mseg~ebeln
  AND nast~kschl = 'NEU'

  ORDER BY mseg~mblnr
           mseg~mjahr
           mseg~zeile
           nast~erdat DESCENDING
           nast~eruhr DESCENDING.

DELETE ADJACENT DUPLICATES FROM gt_sel COMPARING mblnr mjahr zeile.