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: 

Count on Joined tables

Former Member
0 Kudos

Hi there must be a simple solution - I want to get the number of operations against planner group but the count keeps returning 0 - can someone point me in the right direction please:

select crhd~arbpl count(*) as 'num'

from afvv

inner join kbed

on afvvaufpl = kbedaufpl

inner join crhd

on kbedarbid = crhdobjid

into corresponding fields of table itab

group by crhd~arbpl .

1 ACCEPTED SOLUTION

former_member188685
Active Contributor
0 Kudos

hi,

don't use count, it is performance issue. try to describe after your select.

<b>

data: v_line type i.

describe table itab lies v_line.</b>

regards

vijay

7 REPLIES 7

former_member181962
Active Contributor
0 Kudos

One way is to use count( distinct field ) instead of count(*).

ANother way is to describe the internal table itab.

describe table itab lines v_lines.

REgards,

Ravi

former_member188685
Active Contributor
0 Kudos

hi,

don't use count, it is performance issue. try to describe after your select.

<b>

data: v_line type i.

describe table itab lies v_line.</b>

regards

vijay

0 Kudos

could you elaborate please - I am very new to this (previous .NET SQL-Server), finding this all very frustrating ...

Former Member
0 Kudos

Hi,

Is data is available for such joint into Corresponding tables

Regards

Manoj

Former Member
0 Kudos

Hi Nigel,

In the below code internal table icrhd will have all relevant aufpl values. You need to get <b>count of each aufpl</b> for your requirement.

Regards,

Raj

REPORT ytestraj2.

DATA: BEGIN OF iafvv OCCURS 0,

aufpl LIKE afvv-aufpl,

END OF iafvv.

DATA: BEGIN OF ikbed OCCURS 0,

aufpl LIKE kbed-aufpl,

arbid LIKE kbed-arbid,

END OF ikbed.

DATA: BEGIN OF icrhd OCCURS 0,

arbpl LIKE crhd-arbpl,

END OF icrhd.

START-OF-SELECTION.

SELECT aufpl

FROM afvv INTO TABLE iafvv.

IF sy-subrc = 0.

SORT iafvv BY aufpl.

DELETE ADJACENT DUPLICATES FROM iafvv COMPARING aufpl.

ENDIF.

IF NOT iafvv[] IS INITIAL.

SELECT aufpl arbid

INTO TABLE ikbed

FROM kbed FOR ALL ENTRIES IN iafvv

WHERE aufpl = iafvv-aufpl.

IF sy-subrc = 0.

SORT ikbed BY aufpl arbid.

DELETE ADJACENT DUPLICATES FROM ikbed COMPARING aufpl arbid.

ENDIF.

ENDIF.

IF NOT ikbed[] IS INITIAL.

SELECT arbpl

INTO TABLE <b>icrhd</b>

FROM crhd FOR ALL ENTRIES IN ikbed

WHERE objid = ikbed-arbid.

if sy-subrc = 0.

write:/.

endif.

ENDIF.

Former Member
0 Kudos

Hi,

Changed the logic a little...

Regards,

Raj

&----


*& Report YTESTRAJ2

*&

&----


*&

*&

&----


REPORT ytestraj2.

DATA: BEGIN OF iafvv OCCURS 0,

aufpl LIKE afvv-aufpl,

END OF iafvv.

DATA: BEGIN OF ikbed OCCURS 0,

arbid LIKE kbed-arbid,

aufpl LIKE kbed-aufpl,

END OF ikbed.

DATA: BEGIN OF icrhd OCCURS 0,

arbpl LIKE crhd-arbpl,

END OF icrhd.

DATA: count TYPE i.

START-OF-SELECTION.

SELECT aufpl

FROM afvv INTO TABLE iafvv.

IF sy-subrc = 0.

SORT iafvv BY aufpl.

DELETE ADJACENT DUPLICATES FROM iafvv COMPARING aufpl.

ENDIF.

IF NOT iafvv[] IS INITIAL.

SELECT arbid aufpl

INTO TABLE ikbed

FROM kbed FOR ALL ENTRIES IN iafvv

WHERE aufpl = iafvv-aufpl.

IF sy-subrc = 0.

SORT ikbed BY arbid aufpl.

ENDIF.

ENDIF.

IF NOT ikbed[] IS INITIAL.

SELECT arbpl

INTO TABLE icrhd

FROM crhd FOR ALL ENTRIES IN ikbed

WHERE objid = ikbed-arbid.

IF sy-subrc = 0.

SORT icrhd BY arbpl.

LOOP AT icrhd.

AT NEW arbpl.

count = 0.

ENDAT.

count = count + 1.

AT END OF arbpl.

READ TABLE icrhd INDEX sy-tabix.

WRITE:/ icrhd-arbpl, 18 count.

CLEAR count.

ENDAT.

ENDLOOP.

ENDIF.

ENDIF.

former_member183804
Active Contributor
0 Kudos

Hello Nigel,

your select statement looks fine. But I wonder if the quotes for the alias 'num' are well placed. They are not necessary, maybe there is a slight flaw in the syntax analsis. Can you give it a try and remove them and let us know?

I have done a try with a more common table, and it was working with proper aliasing and failing otherwise.


Report Kzi_test_01.

  types:
    begin of ty_S_Devclass,
      Name        type Devclass,
      num_Objects type i,
    end of ty_S_Devclass.
  data: devClass type ty_S_Devclass.

  select single a~devclass as name count(*) as num_Objects
    from tadir as a
    inner join tadir as b
    on
      a~devclass =  b~obj_Name  and
      b~object =    'DEVC'      and
      b~obj_Name =  'SABP'
    into corresponding fields of devClass
    group by a~devclass.

  write: / devClass-Num_Objects.

Beside of this possibly a set of individual selects on the tables + some computation might be faster. Any select with joins is a db query and bypasses the SAP buffering. Each application server 'caches' the content of selected table in memory. A select on such a table can use the buffer a join never.

Best Regards

Klaus