Group by with String

[SimonTenbeitel]

Hey,

I need to find out, whether there are more than one entry in a database table with the same values for appl_name and file_path.

So I wrote a sql query like this:

Select appl_name from *NAME OF TABLE* where appl_name like 'Z%' group by appl_name file_path having count(*) > 1.

Unfortunately file_path is a String, so I get an error message, that it's not allowed in the group by.

How can I fix this?

Best regards

Simon!

[Former Member]

Hi Simon,

I think if you first select and then sort the internal table by appl_name and file_path, then it will solve your purpose:

DATA: lv_index TYPE sytabix.

SELECT      appl_name

                  file_path           

from <TABLE_NAME>

INTO <internal_table>

WHERE appl_name like "Z%".

IF sy-subrc is INITIAL.

     SORT <internal_table> BY appl_name file_path.

     LOOP AT <internal_table> ASSIGNING <field-symbol>.

          lv_index = sy-tabix + 1.

          READ TABLE <internal_table> ASSIGNING <field_symbol2> INDEX lv_index

          IF sy-subrc IS INITIAL AND <field-symbol2>-appl_name = <field-symbol>-appl_name

          AND <field-symbol2>-file_path = <field-symbol>-file_path.

**********Current appl_name & file_path combination is having more than 1 records********* (You can populate a internal table with this information for further processing)

          ENDIF.

     ENDLOOP.

ENDIF.

Hope this helps.

Thanks,

Arnab

[former_member209120]

Hi Simon Tenbeitel

I tried like this it is working... any thing wrong correct me...

DATA: carrid TYPE string,
       minimum TYPE p DECIMALS 2,
       maximum TYPE p DECIMALS 2.

SELECT   carrid MIN( price ) MAX( price )
INTO     (carrid, minimum, maximum)
FROM     sflight
WHERE   PLANETYPE LIKE 'A%'
GROUP BY carrid.

   WRITE: / carrid, minimum, maximum.

ENDSELECT.

[Former Member]

Hi Ramesh,

SAP doesn't recommend using SELECT ENDSELECT because of performance issues. Instead we should always select all our data at once and then process the internal table further.

Thanks,

Arnab