topic Re: Min in left outer join condition in Application Development and Automation Discussions

Min in left outer join condition

former_member584971 — Tue, 04 Sep 2018 09:30:35 GMT

Hi everybody,

I'm trying to use MIN in on condition of left outer join in abap , but it's faield;is it possible in abap?

SELECT t1.foo, t2.bar
  FROM table1 AS t1
LEFT OUTER
  JOIN table2 AS t2
    ON t2.table1_id = t1.id  
   AND t2.datefld = 
       ( SELECT MIN(datefld)
           FROM table2
          WHERE table1_id = t1.id )

thanks !

Re: Min in left outer join condition

RaymondGiuseppi — Tue, 04 Sep 2018 09:59:14 GMT

Read the online Abap documentation (In 'ON sql_cond' is written 'Subqueries cannot be used') so did you try a WHERE clause such as

  SELECT SINGLE mara~matnr marc~werks
    INTO CORRESPONDING FIELDS OF record
    FROM mara
    JOIN marc
      ON mara~matnr EQ marc~matnr
    WHERE
      marc~werks = ( SELECT MIN( werks ) FROM marc WHERE matnr = mara~matnr ).

Re: Min in left outer join condition

former_member584971 — Tue, 04 Sep 2018 13:14:47 GMT

Hi Raymond,

MIN( werks )FROM marc WHERE matnr = mara~matnr )

in where clause change the outcome , i have " left outer join " ; if no records in t2 for where condition , sy-subrc = 4.

Maybe ,

Re: Min in left outer join condition

DoanManhQuynh — Wed, 05 Sep 2018 00:40:00 GMT

I think you can select min(..) to get the min record, like this:

SELECT 

 SPFLI~CARRID AS CARRID,

 SPFLI~CONNID AS CONNID,

 MIN( DISTINCT SFLIGHT~SEATSOCC ) AS SEATSOCC

 FROM SPFLI LEFT OUTER JOIN SFLIGHT ON ( SPFLI~CARRID = SFLIGHT~CARRID AND 

 SPFLI~CONNID = SFLIGHT~CONNID )

 INTO TABLE @DATA(SFLIGHT_TAB)

 GROUP BY SPFLI~CARRID,SPFLI~CONNID.

Re: Min in left outer join condition

former_member584971 — Thu, 06 Sep 2018 14:04:56 GMT

Well done Quynh Doan Manh, u 're right; thanks !