Count on Joined tables

[Former Member]

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 afvv_{aufpl = kbed}aufpl

inner join crhd

on kbed_{arbid = crhd}objid

into corresponding fields of table itab

group by crhd~arbpl .

[former_member181962]

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]

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

[Former Member]

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

[Former Member]

Hi,

Is data is available for such joint into Corresponding tables

Regards

Manoj

[Former Member]

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]

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]

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