Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic Count

Former Member
0 Kudos
1,103

Hi,

I want to do something like this:

 
SELECT COUNT( DISTINCT (select) ) INTO count_aux
FROM (pa_tab)
WHERE (where-where_tab).

Where the tabçe select has the name of the fields i want to distinguish in count operation.

This gives me a dump during runtime of my program, that he don't know the fields in SELECT clause.

Is it possible? How?

Regards,

Pedro Bessa

16 REPLIES 16

I355602
Advisor
Advisor
0 Kudos
325

Hi,

Use:


SELECT COUNT( <field_name> ) INTO (count_aux)
FROM <database_table>
WHERE <condition>.

Hope this helps you.

Regards,

Tarun

Former Member
0 Kudos
325

I think it doesn't.

If i use something like this:


SELECT DISTINCT (select)
FROM (pa_tab)
WHERE (where-where_tab).

It runs and gives me what i want, the problem is in the COUNT statement.

Regards,

0 Kudos
325

Hi,

What is pa_tab, is it internal table or what?

Regards,

Tarun

Former Member
0 Kudos
325

Its a variable:

pa_tab LIKE dd02l-tabname OBLIGATORY

select TYPE edpline OCCURS 0 WITH HEADER LINE.

Example:



pa_tab = 'SFLIGHT'.

select = 'CARRID'.
APPEND select.


select = 'CONNID'.
APPEND select.

    SELECT COUNT( DISTINCT (select) ) INTO int_aux
    FROM (pa_tab).

Former Member
0 Kudos
325

Well i think we can only use one field in Count ( Distinct ).

But if I have a variable with the name of the field that i want to put, anyone knows how i can do it?

Something like this


DATA: field(20) TYPE c,
tab(20)   TYPE c,
 count      TYPE i.

field = 'CARRID'.

tab= 'SFLIGHT'.

SELECT COUNT( DISTINCT (field) ) INTO count
FORM (tab).

Regards,

Pedro Bessa

0 Kudos
325

Hi,

I think Dynamic program execution will solve your problem,

Check this logic:

tables: dd02l, dd03l.

data: count_aux type n.
data: select type string.

data: begin of itab occurs 0,
        line(128),
        end of itab.

data: v_name like sy-repid.

parameters: pa_tab like dd02l-tabname,
            field like dd03l-fieldname.

itab-line = 'Report sy-repid.'.
append itab. clear itab.
itab-line = 'tables:'.
append itab. clear itab.
concatenate pa_tab '.' into itab.
itab-line = 'data: count type i.'.
append itab. clear itab.
itab-line = 'form f_count.'.
append itab. clear itab.
itab-line = 'select count( DISTINCT'.
append itab. clear itab.
concatenate field ') INTO COUNT' INTO itab separated by space.
concatenate 'FROM' pa_tab '.' INTO itab separated by space.
itab-line = 'endform.'.
append itab. clear itab.

generate subroutine pool itab name v_name.
perform f_count in program (v_name).

thanks\

Mahesh

0 Kudos
325

thanks\

Mahesh

Edited by: Mahesh Reddy on Mar 10, 2009 1:37 PM

Former Member
0 Kudos
325

Thats one way to go, and it will solve my problem.

Thanks,

0 Kudos
325

Cheers\

Former Member
0 Kudos
325

If anyone know that its possible to do it in the way i mencioned, please tell me.

dirk_meyer2
Participant
0 Kudos
325

Hi Pedro.

I am working on a program to analyse loaded data in PSA or DSO table. I want to count values in each column and stumbled over the same problem as you did.

I tried the subroutine version. This works but has a limitation since you are only allowed to start 36 during the program execution.

Since I had to check all available colums I tried the approach below - which did not allways work for that very reason.

Did you find any other way in the mean time?

Cheers

Dirk

---------------------------------------------

----------------------------------------------

loop at lt_fields into LS_fields.

  L_field = LS_fields-fieldname.

clear l_count.

* Einträge zählen und speichern

*select count( DISTINCT <L_field> ) INTO L_COUNT FROM (p_table) . - not working

clear itab.

append 'PROGRAM subpool.' to itab.

*append 'data:  l_count type i,' to itab.

append 'INCLUDE ZBW_COUNTER.' to itab.

append 'LOAD-OF-PROGRAM.' to itab.

APPEND 'FORM F_COUNT.' to itab.

clear L_line.

concatenate 'select count( DISTINCT' L_field ') INTO L_COUNT ' into L_line separated by space.

append L_line to itab.

clear L_line.

concatenate 'FROM' p_table '.' INTO L_line separated by space.

append L_line to itab.

clear L_line.

append 'ENDFORM.' to itab.

generate subroutine pool itab name v_name.

IF sy-subrc = 0.

perform ('F_COUNT') in program (v_name).

ELSEIF sy-subrc = 4 or sy-subrc = 8.

   status = 'S'.

   RETURN.

*  MESSAGE mess TYPE 'I'.

*ELSEIF sy-subrc = 8.

*  MESSAGE sid TYPE 'I'.

ENDIF.

LS_fields-entries = l_count.

GET TIME STAMP FIELD timestamp.

ls_fields-TIMESTAMP = timestamp.

modify lt_fields from ls_fields.

endloop.

Former Member
0 Kudos
325

Hi Pedro,

Try following code

data:pa_tab    LIKE dd02l-tabname OBLIGATORY

data:select      TYPE String.

FIELD-SYMBOLS: <f1> TYPE ANY.

pa_tab = 'SFLIGHT'.

select = 'CARRID'.

ASSIGN select TO <f1>.

    SELECT COUNT( DISTINCT (<f1>) ) INTO itab

    FROM (pa_tab).

0 Kudos
325

Hi Shantosh,

Thanks for the input.

Unfortunately it created the same error as with a normal variable.

----

An Open SQL clause was specified dynamically. The contained field name

"(<F1>)" does not exist in any of the database tables from the FROM clause.

----

as well as leaving the breaket away:

----

An Open SQL clause was specified dynamically. The contained field name

"<F1>" does not exist in any of the database tables from the FROM clause.

----

It seems that the COUNT DISTINCT can not read any kind of varible.

Regards

Dirk

0 Kudos
325

Hi,

you mean you want something like this:

DATA: field TYPE string,
            tab   TYPE string,
            count TYPE i.

  field = 'COUNT( DISTINCT carrid )'.
  tab = 'SPFLI'.

  SELECT (field)
         FROM (tab)
         INTO count.
  ENDSELECT.
  WRITE: count.

Cheers,

Manu.

0 Kudos
325

Sooo sweet and simple.

Thanks a lot Manu

Former Member
0 Kudos
325

Hello Pedro,

If you are using the same for the ALV report, you can also use the system field viz. SY-DBCNT = SY-DBCNT + 1, which also works like the dynamic count.

It will also work fine and may fulfill your requirement.

Regards,

Akg