topic Re: Dynamic Count in Application Development and Automation Discussions

Dynamic Count

Former Member — Mon, 09 Mar 2009 11:14:02 GMT

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

Re: Dynamic Count

I355602 — Mon, 09 Mar 2009 11:18:47 GMT

Hi,

Use:


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

Hope this helps you.

Regards,

Tarun

Re: Dynamic Count

Former Member — Mon, 09 Mar 2009 11:26:48 GMT

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,

Re: Dynamic Count

I355602 — Mon, 09 Mar 2009 11:49:07 GMT

Hi,

What is pa_tab, is it internal table or what?

Regards,

Tarun

Re: Dynamic Count

Former Member — Mon, 09 Mar 2009 11:53:14 GMT

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

Re: Dynamic Count

Former Member — Tue, 10 Mar 2009 12:08:59 GMT

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

Re: Dynamic Count

former_member222860 — Tue, 10 Mar 2009 12:36:31 GMT

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

Re: Dynamic Count

former_member222860 — Tue, 10 Mar 2009 12:36:55 GMT

thanks\

Mahesh

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

Re: Dynamic Count

Former Member — Tue, 10 Mar 2009 12:39:19 GMT

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

Thanks,

Re: Dynamic Count

former_member222860 — Tue, 10 Mar 2009 12:40:15 GMT

Cheers\

Re: Dynamic Count

Former Member — Tue, 10 Mar 2009 12:42:03 GMT

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

Re: Dynamic Count

dirk_meyer2 — Wed, 23 May 2012 10:13:06 GMT

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.

Re: Dynamic Count

Former Member — Wed, 23 May 2012 11:00:21 GMT

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

Re: Dynamic Count

Former Member — Wed, 23 May 2012 11:20:07 GMT

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

Re: Dynamic Count

dirk_meyer2 — Wed, 23 May 2012 12:01:47 GMT

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

Re: Dynamic Count

Former Member — Wed, 23 May 2012 21:42:10 GMT

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.

Re: Dynamic Count

dirk_meyer2 — Fri, 25 May 2012 07:18:44 GMT

Sooo sweet and simple.

Thanks a lot Manu