Skip to Content
-2

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

Jul 25, 2017 at 11:51 AM

53

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

avatar image
Former Member Jul 25, 2017 at 12:30 PM
0

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.
Share
10 |10000 characters needed characters left characters exceeded