topic Re: select count in Application Development and Automation Discussions

select count

Former Member — Tue, 25 Mar 2008 08:02:04 GMT

Hi,

i wont to do select count but with for all entries there is way to

do it in different way because in table proj i have many projects?

Regards

SELECT  dimybproject COUNT(*) AS cnt
  FROM /bic/0czempl
  INTO CORRESPONDING FIELDS OF TABLE it_e
  FOR ALL ENTRIES IN proj
  WHERE dimybproject EQ proj-project   
  GROUP BY dimybproject.

Re: select count

Former Member — Tue, 25 Mar 2008 08:11:51 GMT

Hi,

after the query has been executed u will get the Number of Records in the sy-dbcnt that will give u the count.

Regards,

Balakumar.G

Reward Points if helpful.

Re: select count

Former Member — Tue, 25 Mar 2008 08:14:43 GMT

Hi,

i try to do like i write above and i have error that i cant use for all enries for select count

Regards

Re: select count

Former Member — Tue, 25 Mar 2008 08:15:15 GMT

Hi Ricardo,

There is a way to achieve your functionality but not like the way that you've mentioned above.

you need to populate the table using Select ...Endselect within which you need to populate the project name into the internal table. i.e.. write only count(*) inside the Select query. But this is an obsolete and a worst way to achieve this.

Otherwise, you need to write a logic after populating the internal table with project names for counting them.

Or you add a field which is an integer and collect the internal table so that that field adds up to the count.

Reward points if this helps,

Kiran

Re: select count

Former Member — Tue, 25 Mar 2008 08:16:19 GMT

Hi,

this is the error :

The addition "FOR ALL ENTRIES" excludes all aggregate functions with the exception of "COUNT( * )" as the single element of the SELECT clause.

Regards

Re: select count

Former Member — Tue, 25 Mar 2008 08:18:48 GMT

check for the value in sy-dbcnt after getting the entries in

internal table ..

Re: select count

Former Member — Tue, 25 Mar 2008 08:32:58 GMT

Hi Ricardo, Treat this as a limitation from SAP as FOR ALL ENTRIES can not accept few clauses like ORDER BY, HAVING... I doubt if we can find a way to go without further manipulations. Though it is a bit tedious two ways to acheive the result as you might be knowing are: 1. Build a range for Project and use the same avoiding FOR ALL ENTRIES 2. LOOP on first internal table and use APPENDING table instead of INTO TABLE.

Re: select count

Former Member — Tue, 25 Mar 2008 08:39:26 GMT

You can just count the number of records in it_e

ie,

Data :

count type i.

Describe table it_e LINES COUNT.

Now count contains the number of records.

Re: select count

Former Member — Tue, 25 Mar 2008 08:48:38 GMT

hi ,

check this example.....

tables:pa0008.

data: begin of itab occurs 0,

pernr like pa0008-pernr,

begda like pa0008-begda,

ansal like pa0008-ansal,

count type i,

end of itab.

select-options:s_pernr for pa0008-pernr.

select pernr

count(*) as count

from pa0008

into corresponding fields of table itab

where pernr in s_pernr

group by pernr .

sort itab by pernr.

loop at itab.

write:/ itab-pernr,

itab-count .

endloop.

regards,

venkat.

Re: select count

Former Member — Tue, 25 Mar 2008 08:53:42 GMT

hi frinds,

Thanks i solve it with ranges,

LOOP AT proj ASSIGNING <fs_p>.

    proj_tab-sign = 'I'.
    proj_tab-option = 'EQ'.
    proj_tab-low = <fs_p>-ybproject.

    APPEND p_tab.

  ENDLOOP.



  SELECT  dimybproject COUNT(*) AS cnt
  FROM /bic/0czempl
  INTO CORRESPONDING FIELDS OF TABLE it_e
  WHERE dimybproject IN p_tab
  GROUP BY dimybproject.

Regards