Min in left outer join condition

[former_member584971]

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 !

[former_member584971]

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

[RaymondGiuseppi]

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

[former_member584971]

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 ,

[DoanManhQuynh]

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.

[former_member584971]

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