Fetch duplicate records from database table

[shubhankar2]

Hello folks,

I am getting error in below query as Field "COUNT(*)" unknown I am trying to fetch duplicate records from database table or else any other way.

SELECT profile PROFILE_TYPE LOGQS logsys ddpid COUNT(*) 
    INTO TABLE st_ddph_e
      FROM /ctscm/md_ddph
      GROUP BY profile 
               PROFILE_TYPE 
               LOGQS
      HAVING COUNT(*) > 1.

[Sandra_Rossi]

What error? Syntax error? "The field "LOGSYS" from the SELECT list is missing in the GROUP BY clause"?

[shubhankar2]

Hello sandra, getting syntax error as "Field "COUNT(*)" unknown"

[Sandra_Rossi]

Apologies, my bad. What happens if you remove "logsys ddpid"?

[shubhankar2]

Hello sandra, I am getting same error if i remove logsys ddpid I belive this syntax will work only in opensql?

[Sandra_Rossi]

This below code (that anybody can test) compiles in a class in an ABAP program in Standard ABAP. It will fail if you enter this ABAP SQL statement in a non-ABAP code.

    TYPES: BEGIN OF ts_sql,
             carrid TYPE s_carr_id,
             count  TYPE i,
           END OF ts_sql.
    DATA lt_sql TYPE TABLE OF ts_sql.
    SELECT carrid COUNT(*) INTO TABLE lt_sql FROM sflight 
        GROUP BY carrid HAVING COUNT(*) > 1.

The below code works with ABAP for Cloud development:

        TYPES: BEGIN OF ts_sql,
                 product TYPE i_product-product,
                 count   TYPE i,
               END OF ts_sql.
        DATA lt_sql TYPE TABLE OF ts_sql.
        SELECT product, COUNT(*)
          FROM i_product
          GROUP BY product
          HAVING COUNT(*) > 1
          INTO TABLE @LT_sql.