Skip to Content
-2

SAP Open SQL, "WHERE (field1, field2) IN ..." clause.

Should I be able to use SQL code in my ABAP something like this:

SELECT matnr, ersda, erzzt
FROM  Ztable...
WHERE (matnr, ersda, erzzt) IN ( SELECT matnr, ersda, erzzt
                                   FROM Ztable...
                                   ORDER BY matnr, ersda, erzzt DESCENDING )
GROUP BY matnr
HAVING COUNT(*) = 1.

As an open SQL statement that's probably okay, but I'm not sure if I can use multiple fields in the WHERE ... IN clause using SAP OPEN SQL ;).

Has anyone managed to get something like this to work.

So, in my case there could be dupluicate materials, but with different date/times fieldas. I want the materials with the latest date/time, so that the materials are unique.

Any suggestions. I know that I could just retrieve the data back and do similar logic within the ABAP, but, I should not have to. I would have no trouble if I were using Oracle SQL, and I can't use Native SQL either, it's not permitted. ;)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jul 25, 2017 at 12:30 PM

    I have a solution, although not solely through using SQL.

    "SQL returns all records, but in the latest order of material, date, time
    SELECT MATNR,
            ERSDA,
            ERZZT
     FROM ZTable...
     WHERE ZSTAGEFIELD EQ 'MTART'
     ORDER BY matnr, ersda, erzzt DESCENDING
     INTO CORRESPONDING FIELDS OF TABLE @gt_matnr_process.
     IF sy-subrc NE 0.
        MESSAGE E008. "Error when accessing table ZTable...!
     ENDIF.
     "Now remove duplicates from sorted list (Descending)
     DELETE ADJACENT DUPLICATES FROM gt_matnr_process COMPARING matnr.
    Add comment
    10|10000 characters needed characters exceeded