2006 Apr 11 4:30 PM
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 .
2006 Apr 11 4:37 PM
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
2006 Apr 11 4:35 PM
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
2006 Apr 11 4:37 PM
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
2006 Apr 11 4:49 PM
could you elaborate please - I am very new to this (previous .NET SQL-Server), finding this all very frustrating ...
2006 Apr 11 4:38 PM
Hi,
Is data is available for such joint into Corresponding tables
Regards
Manoj
2006 Apr 11 5:02 PM
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.
2006 Apr 11 5:32 PM
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.
2006 Apr 11 7:46 PM
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