How can i turn this sql statement into an abap statement?

Question

Select knvp.KUNNR, kna1.NAME1, knvp.PARVW, knvp.KUNN2
from landing.kna1 kna1,
landing.knvp knvp,
(Select kna1.KUNNR, COUNT(knvp.KUNN2) as count
from landing.KNA1 kna1
, landing.KNVP knvp
where kna1.KUNNR = knvp.KUNN2
and kna1.KTOKD in('ZPYR', 'ZBPR')
group by kna1.kunnr
having count(knvp.kunn2) < 2)as orphans
where kna1.KUNNR = knvp.KUNNR
and knvp.KUNNR = orphans.KUNNR

Basically this series of sql statements puts a counter on the kunnr table type and display all the numbers that only show up once in that table.

---

A pretty-printed version of the SQL-statement (This version is attached - just in case some important details get lost from the pretty printer):

SELECT knvp.kunnr, 
       kna1.name1, 
       knvp.parvw, 
       knvp.kunn2 
FROM   landing.kna1 kna1, 
       landing.knvp knvp, 
       (SELECT kna1.kunnr, 
               Count(knvp.kunn2) AS count 
        FROM   landing.kna1 kna1, 
               landing.knvp knvp 
        WHERE  kna1.kunnr = knvp.kunn2 
               AND kna1.ktokd IN ( 'ZPYR', 'ZBPR' ) 
        GROUP  BY kna1.kunnr 
        HAVING Count(knvp.kunn2) < 2) AS orphans 
WHERE  kna1.kunnr = knvp.kunnr 
       AND knvp.kunnr = orphans.kunnr 




im starting with this

rowNum     col1    col2    col3     col4
1          1234     bp     name1    1234
2          1234     py     name1    1234
3          1223     bp     name2    1223
4          1245     py     name3    1245
5          5432     py     name4    4432

i want to delete row# 1 and 2 because they have the same col1# but
different col2#

result:

rowNum     col1    col2    col3     col4
1          1223     bp     name2    1223
2          1245     py     name3    1245
3          5432     py     name4    5432

Answer 1

You could try

SELECT knvp~kunnr
       kna1~name1
       knvp~parvw
       knvp~kunn2
FROM kna1 JOIN knvp ON kna1~kunnr = knvp~kunnr
INTO TABLE lt_some_table_with_matching_structure
WHERE knvp~kunnr IN ( SELECT kna1~kunnr
                      FROM kna1 JOIN knvp ON kna1~kunnr = knvp~kunn2
                      WHERE ( kna1~ktokd = 'ZPYR' OR kna1~ktokd = 'ZBPR' )
                      GROUP BY kna1~kunnr
                      HAVING COUNT( DISTINCT knvp~kunn2 ) < 2 ).

but be aware that this is largely guesswork since I don't understand what the query is supposed to do at all.

EDIT: With the additional info from your comment, you might want to try something like this:

DATA: lt_partners TYPE TABLE OF kunnr. " unsure whether this is the correct type

SELECT kunnr 
  FROM kna1
  INTO TABLE lt_partners
  WHERE ( kna1~ktokd = 'ZPYR' OR kna1~ktokd = 'ZBPR' ).
  " AND probably some other filter criteria
SORT lt_partners.
DELETE ADJACENT DUPLICATES FROM lt_partners. " avoid DISTINCT if you can to take load off the database

IF lt_partners IS NOT INITIAL.
  SELECT knvp~kunnr
         kna1~name1
         knvp~parvw
  FROM kna1 JOIN knvp ON kna1~kunnr = knvp~kunnr
  INTO TABLE lt_some_table_with_matching_structure
  FOR ALL ENTRIES IN lt_partners
  WHERE knvp~kunn2 = lt_partners-table_line
  GROUP BY knvp~kunnr kna1~name1 knvp~parvw
  HAVING COUNT( DISTINCT knvp~kunn2 ) < 2.
ENDIF.

Be aware that you can't select KNVP~KUNN2 in this step because you'd have to include it in the GROUP BY clause, and then the HAVING clause wouldn't work. (I don't have a system available right now, so again, this is guesswork...)