Dynamic Count

[Former Member]

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

[I355602]

Hi,

Use:

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

Hope this helps you.

Regards,

Tarun

[Former Member]

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,

[I355602]

Hi,

What is pa_tab, is it internal table or what?

Regards,

Tarun

[Former Member]

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]

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

[former_member222860]

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

[former_member222860]

thanks\

Mahesh

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

[Former Member]

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

Thanks,

[former_member222860]

Cheers\

[Former Member]

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

[dirk_meyer2]

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]

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).

[dirk_meyer2]

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

[Former Member]

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.

[dirk_meyer2]

Sooo sweet and simple.

Thanks a lot Manu

[Former Member]

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