topic Group by with String in Application Development and Automation Discussions

Group by with String

SimonTenbeitel — Tue, 19 Nov 2013 08:28:37 GMT

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!

Re: Group by with String

Former Member — Tue, 19 Nov 2013 08:55:56 GMT

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

Re: Group by with String

former_member209120 — Tue, 19 Nov 2013 08:57:21 GMT

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.

Re: Group by with String

Former Member — Tue, 19 Nov 2013 09:00:55 GMT

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