2009 Mar 09 11:14 AM
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
2009 Mar 09 11:18 AM
Hi,
Use:
SELECT COUNT( <field_name> ) INTO (count_aux)
FROM <database_table>
WHERE <condition>.
Hope this helps you.
Regards,
Tarun
2009 Mar 09 11:26 AM
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,
2009 Mar 09 11:49 AM
Hi,
What is pa_tab, is it internal table or what?
Regards,
Tarun
2009 Mar 09 11:53 AM
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).
2009 Mar 10 12:08 PM
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
2009 Mar 10 12:36 PM
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
2009 Mar 10 12:36 PM
2009 Mar 10 12:39 PM
2009 Mar 10 12:40 PM
2009 Mar 10 12:42 PM
If anyone know that its possible to do it in the way i mencioned, please tell me.
2012 May 23 11:13 AM
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.
2012 May 23 12:00 PM
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).
2012 May 23 1:01 PM
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
2012 May 23 10:42 PM
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.
2012 May 25 8:18 AM
2012 May 23 12:20 PM
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