Selecting and Working with Data (Internal Tables Again)

1. Selecting and Working with Data(Internal Tables Again) MINS298c Fall 1998 Chapters 10-12

2. Overview • Subroutines • Internal Tables Again • Select • all, some, part • Working with it once you got it • Loop • Read • Forms • Study Hints

3. Subroutines (Normal Scope) Line Program DATA A, B, C According to normal rules of “scope,” which Write statements are legal and which are not? 1 2 3 4 5 6 Write A Write F Form DATA X, Y, Z Write A Write F Form DATA D, E, F Write A Write F

4. Subroutines • Defined by FORM and ENDFORM • Executed with PERFORM verb • Can call by value or call by reference • by reference is implied • VALUE clause passes by value • ABAP does number and type checking at runtime

5. Example ASSUME: x, y, and z are integer and x = 4; y = 5; z =10 Write x, y, z. Perform add_em changing value(x) y changing z. Write x, y, z. Form add_em a1 a2 changing a3. a1 = a1 + 1. a2 = a2 + 2. a3 = a1 + a2.

6. Internal Tables • TABLES defines which external database table your program uses • OCCURS statement creates an internal table • The clause WITH HEADER LINE creates a work area (WA) structure defined like the table

7. Header lines tables flight data: my_flight like flight occurs 0 times with header line. my program flight table

8. Header lines tables flight data: my_flight like flight occurs 0 times with header line. my program flight flight table

9. Header lines tables flight data: my_flight like flight occurs 0 times with header line. my program flight flight table my_flight [my_flight]

10. Getting Data • SELECT … INTO • SELECT … APPENDING • INSERT … INDEX n • MOVE

11. SELECT … INTO SELECT * FROM FLIGHT INTO TABLE MY_FLIGHT. my program flight flight table my_flight [my_flight]

12. SELECT … INTO SELECT * FROM FLIGHT INTO TABLE MY_FLIGHT. my program flight flight table my_flight [my_flight]

13. SELECT … INTO SELECT * FROM FLIGHT INTO TABLE MY_FLIGHT. my program flight flight table my_flight [my_flight]

14. SELECT … INTO SELECT * FROM FLIGHT INTO TABLE MY_FLIGHT. my program flight flight table my_flight [my_flight]

15. SELECT … INTO SELECT * FROM FLIGHT INTO TABLE MY_FLIGHT. my program flight flight table my_flight [my_flight]

16. SELECT … INTO SELECT * FROM FLIGHT INTO TABLE MY_FLIGHT. my program flight flight table my_flight [my_flight]

17. SELECT … INTO SELECT * FROM FLIGHT INTO TABLE MY_FLIGHT. my program flight flight table my_flight [my_flight]

18. SELECT … INTO SELECT * FROM FLIGHT INTO TABLE MY_FLIGHT. my program flight flight table my_flight [my_flight]

19. Working Backwards Given the code below, what are the likely data, table or type statements required. Identify the tables, the WAs, and data types SELECT * FROM CUSTOMERS. MOVE-CORRESPONDING CUSTOMERS TO CUSTOMER_CITIES. APPEND CUSTOMER_CITIES. ENDSELECT.

20. SELECT and the WHERE clause • The WHERE clause limits the records selected to those matching the condition(s). • In general, a condition is composed of table-name operator value • Standard Operators • eq, ne, lt, le, gt, ge • New Operators • between, like, in (list), selopt (Select-Option)

21. WHERE Examples WHERE table-name operator value WHERE order_date = ‘19990101’ WHERE cust_name like ‘E%’ WHERE cust_name line ‘B_ll’ WHERE cust_name like ‘E%’ AND order_date = ‘19990101’

22. SELECT statistics • System will keep track of and report some basic statistics based upon your SELECT statement • Statistics • COUNT counts valid records • MAX, MIN, AVG, SUM results on numeric fields • Usage • SELECT COUNT(*) FROM customers • SELECT MAX(inv-owed) FROM invoices

23. Header vs Table SELECT * FROM CUSTOMERS INTO TABLE ALL_CUSTOMERS. SELECT SINGLE * FROM CUSTOMERS WHERE ID = ‘00000017’. All_Customers Header customers Body All_Customers Header customers Body

24. Selecting Fields • SELECT db-field1 db-field2 INTO (itab-field1, itab-field2) FROM db-table. SELECT id name INTO (cid,cname) FROM customers cid, cname

25. Selecting Fields • ABAP uses the domains or data to permit selections by the user • Identify the variables you wish filled in and ABAP builds the interface at runtime • SELECT-OPTIONS command

26. How to Work with Internal Tables • Sorting • SORT itab • SORT itab BY field1 field2 … . • Looping • uses header line or INTO clause • LOOP AT itab. • can be nested • can be selective • Read

27. SORT itab BY field1 field2 … . SORT my_table BY field1 field2 DESCENDING field1 field2 ABC ABC ABC DEF XYZ XYZ XYZ 3 2 1 8 5 3 1

28. LOOP AT itab. LOOP AT My_table. WRITE: / My_Table-field1 ENDLOOP. my_table [my_table] sy-tabix

29. LOOP AT itab. LOOP AT My_table. WRITE: / My_Table-field1 ENDLOOP. my_table [my_table] sy-tabix

30. LOOP AT itab. LOOP AT My_table. WRITE: / My_Table-field1 ENDLOOP. my_table Field1 [my_table] sy-tabix

31. LOOP AT itab INTO wa. LOOP AT My_table INTO holder. WRITE: / My_Table-field1 ENDLOOP. holder [my_table] sy-tabix

32. LOOP AT itab INTO waWHERE condition. LOOP AT My_table INTO holder WHERE amount_owed > ‘1000’. WRITE: / My_Table-field1 ENDLOOP. holder [my_table] sy-tabix

33. READ TABLE itab • WITH KEY field condition • finds first occurrence of key field which matches the condition • can use BINARY SEARCH clause for binary search • INDEX n • direct read of the record at the location n

34. Study Hints • Concepts based • structures, fields, tables • multi-language • Verb basics • Types • Tables • with and w/o header lines • how to get data in • how to get data out • Simple loops

35. In Class Assignment • Write a program to identify the following information about the TABLE sflight: • average prices of tickets • the highest and lowest tickets available • number of records in the table.