How to get results from one's output? (loop within loop)

Question

I am trying to find out the no. of bookings from a table where customer from City = Liverpool; but it seems to give me wrong results. What could have gone wrong?

Tables : scustom, 
         sbook.
Data : ABCtable type scustom,
       BKcnt(4) type N.

Clear BKcnt.

Select * from scustom into ABCtable where city = 'Liverpool'.

  Select * from sbook.
    BKcnt = BKcnt + 1.
  Endselect.

  Write: / ABCtable-id,
             15 ABCtable-name,
             50 BKcnt.
ENDSELECT.

Please try to improve the formatting of your coding. Also, you don't tell us in what way the results appear wrong to you. There are several huge issues in your code. — vwegert, Jul 06 '14 at 13:21

score 1 · Accepted Answer · answered Jul 07 '14 at 05:48

For "What could have gone wrong", see the diagnosis of @knut : You get the same number for each customer, since you always select the complete sbook table.

For questions like this, it is better to leave the aggregation, grouping etc. to the database. Try this version:

report zz_count_sbook.

parameters: p_city type scustom-city lower case default 'Liverpool'.

data: id type scustom-id,
      name type scustom-name,
      count type i.

select customid name count(*) into (id,name,count)
       from scustom as c
       join sbook as b
       on b~customid = c~id
       where city eq p_city
       group by customid name.

  write: /    id,
           15 name,
           50 count.
endselect.

score 0 · Answer 2 · answered Jul 06 '14 at 18:19

Your Select * from sbook. doesn't contain any additional condition. So you count in each loop all entries in sbook, not only the one connected to your entry in scustom.

I don't know tables, so I can't give you the correct select.

You count is not efficient, you don't need to count yourself:

Tables : scustom, 
         sbook.
Data : ABCtable type scustom,
       BKcnt(4) type N.

Clear BKcnt.

Select * from scustom into ABCtable where city = 'Liverpool'.

  Select count(*) into  BKcnt from sbook
    where <???> = ABCtable-<???>.   "I don't know your keys, replace <???> with the correct fields.

  Write: / ABCtable-id,
             15 ABCtable-name,
             50 BKcnt.
ENDSELECT.

(I hope my select count is correct. Please check it with the syntax checker. At least there is an aggregation function in ABAP!)

Jishnu Janardhanan · Answer 3 · 2015-05-29T19:00:45.940

-1

Always use FOR ALL ENTRIES to join two tables for better performance.

SELECT * FROM scustom INTO TABLE ABCtable WHERE city = 'Liverpool'

SELECT count(*) INTO  BKcnt FROM sbook FOR ALL ENTRIES IN ABCtable
WHERE <???> = ABCtable-<???>

edited May 29 '15 at 19:00

answered Jul 28 '14 at 20:19

Jishnu Janardhanan

63
1
1
6

Notice that ABCtable is not defined as a table, it is just a work area, so you cannot use FOR ALL ENTRIES with it – Gert Beukema May 28 '15 at 17:37

How to get results from one's output? (loop within loop)

3 Answers3