abap chapter 3 open sql internal table. sap system : 3 tier client/server db server sap application...
Post on 26-Dec-2015
241 Views
Preview:
TRANSCRIPT
ABAP Chapter 3
Open SQL Internal Table
SAP System : 3 Tier Client/ServerSAP System : 3 Tier Client/Server
DB Server
SAP Application Server
SAP GUI Presentation
Server
SAP GUISAP GUI
SAP SYSTEM (3 Tier Architecture)SAP SYSTEM (3 Tier Architecture)
Presentation Layer
(Windows based)
Application Layer
(Windows Server/UNIX)
Database Server
Database Layer
(Windows Server/UNIX)
M
SAP Instance
Oracle
Informix
DB2
MS SQL Server
SAP DB/MaxDB
G
Dispatcher
RequestQueue
D D B V S E
SAP Buffer(Shared Mem)
SAP GUI SAP GUI
Database Server
Application Server
Dispatcher
RequestQueue
D D D D…
SAP Buffer
Program
Table
…
1
3
45
68
9
10
Report zpsm1.
Tables customers.
Select single * from
customers where id = 1.
Write: / customers-name.
Execute ABAP statement
Check Program in Program Buffer
7
Load&Gen Program
SQL Request
Send List
Generate Screen(List)Send Request
Request List
2 Search for free WP
Store request to queue
Send request to WP
SAP GUI
SAP System : Dialog Processing
TaskHandler
DYNPRO Processor
ABAP Processor
Local Memory
Memory Space
DB Interface
List buffer
Database Server
Dialog Work Process
Dialog Work Process Architecture
Result Set Memory
Open SQL SELECT ... INSERT ... UPDATE ... DELETE ...
DB InterfaceDB Interface
Data
SAP Application Server
Local Memory
DataData
Dialog WP
TaskHandler
DB Interface
Result Set
DataData
Database Server~ 32 KB in length
ABAP Processor
DYNPRO
Memory Space
List Buffer
Example Tables in DB
carrid connid cityfrom cityto distance
LH 0400 LA NY 100
LH 0402 BK NY 540
SQ 0110 SQ BK 250
id name city
1 John New York
2 Peter Singapore
3 David
London
customersspfli
Example Tables in DB
carrid connid fldate price
LH 0400 20010101
150
LH 0400 20010110
145
LH 0400 20010228
130
SQ 0110 20010226
75
sflight
Select Overview
Select <result> Which Columns?
From <table> Which Table? Into <destination> Where to
place? Where <condition> Which Lines?
Select Statement Select multiple records from
database
Select single record from database
SELECT * FROM customers. …ENDSELECT.
SELECT SINGLE * FROM customers WHERE id = 1. …
Select Multiple Records
Tables spfli. Seclect * from spfli.
write: / - -spfli carrid, spfli connid,spf -li cityto.
endselect.if - sy subrc <> 0.
write: / ‘No Data’.endif.
Dialog WPDialog WPDialog WP
TaskHandler
DYNPRO Processor
ABAP Processor
Database
Local Memory
Memory Space
DB Interface
List buffer
Result Set
SELECT Statement Working Steps
1. Transform open SQL to DB SQL and return result set into result set work area
SELECT * FROM spfli. …ENDSELECT.
SELECT * FROM spfli;
2. Loop with data in result set and transfer each record to work area in memory space
SELECT * FROM spfli. …ENDSELECT.
Table Structure in Memory Space
Select … Into Table Structure
Tables spfli. *Seclect from spfli into spfli.
write: / - -spfli carrid, spfli connid,- -spfli cityfrom, spfli cityto.
endselect.if - sy subrc <> 0.
write: / ‘No Data’.endif.
Select … Into Work AreaData wa like spfli.
*Seclect from spfli into wa. write: / wa- carrid, wa-connid,
wa- cityfrom, wa-cityto.endselect.if - sy subrc <> 0.
write: / ‘No Data’.endif.
Exercise I
customers-id customers-name
customers-city
SELECT with WHERE Clause
Loop Processing with Restriction
Tables spfli. Select * from spfli
where cityfrom = ‘FRANKFURT’. - -write: / spfli carrid, spfli cityto.endselect.
- If sy subrc <> 0. write / ‘no data’.endif.
Select With Range
Tables sflight.Select * From sflight
Where price between 100 and 1000.
Write: / sflight-carrid, sflight-connid, sflight-price.Endselect.
SELECT … With IN List
Tables sflight.Select * From sflight Where price in ( 100, 1000 ). Write: / sflight-carrid, sflight-
connid, sflight-price.Endselect.
Select Single Record
Select Single Record
Tables spfli. *Select single from spfli
where carrid = ‘LH’ and connid = ‘0400’.
- if sy subrc = 0. - -write: / spfli carrid, spfli connid,
- -spfli cityfrom, spfli cityto.else.
write: / ‘Data not found’.endif.
Select Column List
Select * : Example
SELECT *
Reading Selected Column
Data: id like customers-id, name like customers-name, city like customers-city.Select id name city into (id , name, city) from customers. write: / id, name, city.endselect.
- if sy subrc <> 0. write / ‘No Data found’.endif.
Reading Selected Column
Data: begin of wa, id like customers-id, name like customers-name, city like customers-city, end of wa.Select id name city into wa from customers. write: / wa-id, wa-name , wa-city.endselect.
- if sy subrc <> 0. write / ‘No Data found’.endif.
Select Column : Example I
Reading Selected Column
Tables customers.Select id name city into (customers-id, customers-name, customers-city) from customers. write: / customers-id, customers-name, customers-city.endselect.
- if sy subrc <> 0. write / ‘No Data found’.endif.
Select Column : Example II
Corresponding Fields of...
Tables: customers.Select id name city into corresponding fields of customers from customers. Write: / customers-id, customers-name, customers-city.Endselect.
Select Statement : Special Topics
DB Count : SY-DBCNT
Tables customers.Select * from customers. write: / sy-dbcnt, customers-id, customers-name.endselect.
- if sy subrc <> 0. write: / ‘No Data found’.else. write: / sy-dbcnt, ‘Record found’.endif.
SELECT … ORDER BY ...
Tables: spfli.Select * from spfli
Order by cityfrom. Write: / spfli-carrid, spfli-connid,
spfli-cityfrom.Endselect.
SELECT … With Template
Tables customers. Select * From customers
Where name Like ‘_r%’. Write: /
- -customers id,customers name.Endselect.
Aggregate Functions Data: maxdat -like sflight distance,
mindat -like sflight distance, counter type I.
Select COUNT *( ) MIN ( distance ) MAX ( distan ce )
into (counter ,min dat, maxdat) from spfli.
Write: / ‘Count :’ , counter, / ‘Min :’ , mindat, / ‘Max :’ , maxdat.
Aggregate Functions : COUNT,MIN,MAX,AVG and SUM
SELECT … GROUP BY ...Data: carrid like sflight-carrid, mindat Type P Decimals 2, maxdat Type P Decimals 2.Select carrid Min( price ) Max( price ) Into (carrid, mindat, maxdat) From sflight Group by carrid. Write: / carrid, mindat, maxdat.Endselect.
อยากทราบว่า ในแต่ละสายการบ�น มี�ราคาต่��ว่ต่��าส�ดและส�งส�ดเทาไร
carrid connid fldate Price
LH 0400 20010101
150
LH 0400 20010110
145
LH 0400 20010228
130
SQ 0110 20010226
75
sflight
Sub Query
tables customers.select * from customers where id <> 1 and city = ( select city from customers where id = 1 ). write: / customers-id, customers-name.endselect.
ลู�กค้�าค้นใดที่ �อยู่��เมื�องเด ยู่วก�บลู�กค้�ารหั�ส ID 1
Exercise I
customers-id customers-name
customers-city
ห้!ามีใช้! SELECT *
Exercise II
usr02-ltimeusr02-trdatusr02-bname
ห้!ามีใช้! SELECT *
ABAP : Inner Join
Tables Join
carrid connid cityfrom cityto distance
LH 0400 NY BK 100
LH 0402 BK NY 540
SQ 0110 SQ BK 250
carrid connid fldate price
LH 0400 20010101
150
LH 0400 20010110
145
LH 0400 20010228
130
SQ 0110 20010226
75
sflightspfli
Tables Join
Question: Select carrid, connid and cityto from spfli and fldate,price from sflight where carrid = ‘LH’
spfli-carrid spfli-connid sflight-fldate spfli-cityto sflight-price
เง#�อนไข : ให้!แสดงข!อมี�ลเฉพาะสายการบ�น ‘LH’ เทาน�'น
Standard SQL
Select spfli.carrid, spfli.connid, sflight.fldate, sflight.price From spfli, sflight Where spfli.carrid = sflight.carrid and spfli.connid = sflight.connid and
spfli.carrid = ‘LH’;
Tables Join Methods Nested select statement Internal table View Inner join of Select statement
Nested Select Statement
Tables: spfli,sflight.Select * from spfli where carrid = ‘LH’. Select * from sflight where carrid = spfli-carrid and connid = spfli-connid. Write: / spfli-carrid, spfli-connid, sflight-fldate, sflight-price. Endselect.Endselect.
Open SQL – Inner JoinTables: spfli,sflight.Select spfli~carrid spfli~connid sflight~fldate spfli~cityto
sflight~price into (spfli-carrid, spfli-connid, sflight-fldate, spfli-cityto, sflight-
price) from spfli inner join sflight on spfli~carrid = sflight~carrid and spfli~connid = sflight~connid where spfli~carrid = ‘LH’. Write: / spfli-carrid, spfli-connid, sflight-fldate, spfli-cityto, sflight-price.Endselect.
Open SQL – Inner JoinTables: A,B.Select A~a B~b B~c into (A-a,B-b,B-c) from A inner join B on A~b = B~b. Write: / A-a,B-b,B-c.Endselect.
a ba1 b1
a2 b2
Table : A
b cb1 c1
b2 c2
b3 c3
Table : B
A-a B-b B-c
Open SQL – Inner Join
a ba1 b1
a2 b2
Table : Ab cb1 c1
b2 c2
b3 c3
Table : B
A~a B~b B~ca1 b1 c1
a2 b2 c2
Single Result Table(Result set)
Select …
inner join..
Endselect.
Database
Server
Application Server
1
2
Open SQL – Alias Table Name
Tables: spfli,sflight.Select a~carrid a~connid b~fldate a~cityto b~price into (spfli-carrid, spfli-connid, sflight-fldate, spfli-cityto, sflight-
price) from spfli as a inner join sflight as b on a~carrid = b~carrid and a~connid = b~connid where a~carrid = ‘LH’. Write: / spfli-carrid, spfli-connid, sflight-fldate, spfli-cityto, sflight-
price.Endselect.
Inner Join/Outer Join Example
id
name city tel
1 John New York 111111
2 Peter London 222222
3 David Singapore
432555
4 Micheal Bangkok 234111
p_id prod_name on_hand
A1 Pen 100
A2 Pencil 125
B1 Ruler 80
X1 Tape 120
Y1 CD 99
cust_id prod_id sale_date
qty sale_id
1 A1 20020318
10 01
1 A2 20020318
50 01
3 X1 20020321
90 02
sale_id name
01 Somchai
02 Pipop
ZPRODUCTS
ZSALES
ZSALEREPS ZCUSTOMERS
Open SQL – Inner Join
REPORT ZINNERJOIN01 .TABLES: ZCUSTOMERS,ZSALES.SELECT A~NAME B~PROD_ID INTO (ZCUSTOMERS-NAME,ZSALES-PROD_ID) FROM ZSALES AS B INNER JOIN ZCUSTOMERS AS A ON B~CUST_ID = A~ID. WRITE: / ZCUSTOMERS-NAME,ZSALES-PROD_ID.ENDSELECT.
Open SQL – Inner Join > 2 Tables
Tables: A,B,C.Select A~a B~c C~y into (A-a,B-c,C-y) from A inner join B on A~b = B~b inner join C on C~x = B~c. Write: / A-a,B-c,C-y.Endselect.
a b… …
Table : A
b c… ...
… ...
… …
Table : B
x y… ...
Table : CA-a B-c C-y
Open SQL – Inner Join > 2 Tables
REPORT ZINNERJOIN02 .TABLES: ZCUSTOMERS,ZPRODUCTS,ZSALES.SELECT A~NAME C~PROD_NAME B~QTY INTO (ZCUSTOMERS-NAME, ZPRODUCTS-PROD_NAME, ZSALES-QTY) FROM ZSALES AS B INNER JOIN ZCUSTOMERS AS A ON B~CUST_ID = A~ID INNER JOIN ZPRODUCTS AS C ON C~P_ID = B~PROD_ID. WRITE: / ZCUSTOMERS-NAME,ZPRODUCTS-PROD_NAME,ZSALES-
QTY.ENDSELECT.
Exercise
List customers who buy product from company as following fields:
zcustomers-id zcustomers-name zsales-sale_date zproducts-prod_name zsales-qty zsalereps-name
Exercise : User Master
USR02-BNAME USR02-TRDAT ADCP-TEL_NUMBER
USR02
USR21
ADCP
BNAME
PERSNUMBER
ADDRNUMBER
Tables Relationship
ABAP : Outer Join
Open SQL – Outer JoinREPORT ZOUTERJOIN .TABLES: ZCUSTOMERS,ZSALES.SELECT A~NAME B~PROD_ID INTO (ZCUSTOMERS-NAME,ZSALES-PROD_ID) FROM ZCUSTOMERS AS A LEFT OUTER JOIN
ZSALES AS B ON A~ID = B~CUST_ID. WRITE: / ZCUSTOMERS-NAME,ZSALES-PROD_ID.ENDSELECT.
A~NAME B~PROD_ID
John A1
John A2
Peter
David X1
Micheal
Single Result Table
Exercise
List customers name who do not buy any product from company
Sub Query
REPORT ZSUBQUERY .tables: zcustomers.select * from zcustomers as a where not exists ( select * from zsales as b where b~cust_id = a~id ). write: / zcustomers-name.endselect.
ลู�กค้�าชื่��ออะไรที่ �ไมื�ได�ซื้� อส!นค้�าจากเรา มื ใค้รบ�าง
Internal Table
Data Objects in ABAPData Objects in ABAP
Memory Space
Structure
Table Structure Internal Table
Variable
Constants<Field-symbols>
INTERNAL TABLEFlight (Structure)
Carrid Connid Date Price
Internal TableFlight (Internal Table)
Carrid Connid Date Price
Header Line
Structure
Data: Begin of flight,carrid -like sflight carrid,connid -like sflight connid,date -like sflight fldate,price -like sflight price.
Data: End of flight.
- flight carrid = ‘LH’.
Write: -/ flight carrid.
INTERNAL TABLEData: begin of tab occurs 10,
carrid like sflight-carrid,connid like sflight-connid,fldate like sflight-fldate,price like sflight-price.
Data end of tab.
USING ABAP DICTIONARY STRUCTURE
Data: begin of tab occurs 0.Include structure sflight.
Data end of tab.
INTERNAL TABLE USING LIKE
Data tab LIKE sflight OCCURS 0 WITH HEADER LINE.
FILLING INTERNAL TABLE (APPEND)
Tables sflight.Data flight like sflight occurs 0 with header line.Select * from sflight. Move sflight to flight. Append flight.Endselect.
Standard Key of Internal Table
Data: begin of tab occurs 0, f1 type C, f2 type I, f3 type N, f4 type P, end of tab.
f1 f2 f3 f4
tab
Reading Data From Internal Table
0Data tab like sflight occurs with header line. ** **** ******* **** ***** ***.** **-***** = 0. ** ****. - *** -******: / , . .Else. Write: / ‘No Data’.Endif.
Access Database Without Internal Table
Access Database Using Internal Table
Reading Data From Internal Table****: begin of ****** *0 , id like customers-id, name like customers-name, end of tab.
Select id name from customers **** ***** ***.If sy-subrc = 0. Loop at tab. - Write: / tab id, tab-name. Endloop.else. Write: / ‘ ’ .Endif.
Exercise I : Change
Using Internal Table
SORTING INTERNAL TABLE (SORT)
Sort flight.Sort flight by price fldate.Sort flight by price ascending
fldate descending.
Data tab like spfli occurs 0 with header line.
Select * from spfli into table tab.Sort tab by cityfrom.…Loop at tab. write: / tab-carrid, tab-connid,tab-cityfrom.Endloop.
SORTING INTERNAL TABLE
PROCESSING INTERNAL TABLE...
Loop at flight. - -Write: / flight carrid, flight connid.Endloop.
Loop at flight where carrid = ‘LH’. -Write: / flight carrid, -flight connid.Endloop. Loop at flight from 1 to 10. Write: / -sy tabix , -flight carrid, -flight connid.Endloop.
Internal Table Template Condition
...loop at tab where name cp ‘+r*’....
Reading Single Record...
Sort flight by carrid connid fldate. Read table flight
with key c arrid = ‘LH’ c onnid = ‘0400’ fldate = ‘19990201’ Binary Search.
- if sy subrc = 0. - -write : / flight carrid,flight connid,
flight-fldate, -flight price. endif.
Reading Single Record using Index
... Read table flight index 3.
- If sy subrc = 0. -write: / flight carrid, flight-connid.
Endif.
CHANGING INTERNAL TABLE
...Delete flight index 5.Delete ffffff f ffff ffffff f fff ff
- flight carrid = ‘XX’.flight-price = 100.… fffff ff1 .
DELETING INTERNAL TABLE
Clear flight.
Refresh flight.
Free flight.
DATA flight LIKE sflight occurs 0 with header line.
Total Record of Internal TableTotal Record of Internal Table
Data: line_count type i.Data tab like sflight occurs 0 with header li
ne.Select * from sflight into table tab.Describe table tab lines line_count.Write: / line_count.
Exercise I
Internal Table Processing
Data tab like spfli occurs 0 with Header line.
…Select * from spfli appending table tab where carrid = ‘LH’.
SELECT … INNER JOIN
REPORT ZINNERJOIN01 .TABLES: ZCUSTOMERS,ZSALES.SELECT A~NAME B~PROD_ID INTO (ZCUSTOMERS-NAME,ZSALES-PROD_ID) FROM ZSALES AS B INNER JOIN ZCUSTOMERS AS A ON B~CUST_ID = A~ID. WRITE: / ZCUSTOMERS-NAME,ZSALES-PROD_ID.ENDSELECT.
Inner Join into Internal Table
REPORT ZJOIN01 .DATA: begin of tab occurs 0, name like zcustomers-name, prod_id like zsales-prod_id, end of tab. SELECT A~NAME B~PROD_ID INTO TABLE tab FROM ZSALES AS B INNER JOIN ZCUSTOMERS AS A ON B~CUST_ID = A~ID.…LOOP AT tab. WRITE: / TAB-NAME,TAB-PROD_ID.ENDLOOP.
Internal Table Without Header LineDATA tab LIKE customers OCCURS 0.
DATA wa LIKE customers.
…
LOOP AT tab INTO wa.
WRITE: / wa-id, wa-name.
ENDLOOP.
Internal Table Declaration
DATA tab TYPE TABLE OF customers.
DATA wa LIKE LINE OF customers.
…
ABAP Practice
Database Table Processing
INSERT UPDATE MODIFY DELETE
Database
Insert (Table)
Tables customers.- customers id = ‘999’.- customers name = ‘Test’.
Insert customers. - if sy subrc <> 0.
****** * ***** ******* ******** endif.
Update Statement
Tables customers.Select single * from customers
where id = 1.If sy-subrc = 0. customers-name = ‘John’. update customers.Endif. Update customers
set name = ‘John’ where id = 1.
Update Statement
Data wa like ****** ****wa- 1id = ‘ ’.wa-nam e = ‘Test No 1’.wa-city = ‘Bangkok’.update customers from wa*If sy-subrc <> 0. write: / ‘Data not found’.Endif.
Modify Statement
Tables customers.- customers id = ‘1’.- customers name = ‘Test No 1’.
* ***** ****** ****
Deleting Database Table Entries
Tables customers.- customers id = ‘1’.
Delete customers.
Delete customers From Tabledelcustomers.
Delete From customers Where city = ‘ Bangkok**
Exercise II
Exercise II
usr02-ltimeusr02-trdatusr02-bname
1. ห้!ามีใช้! SELECT * 2. ใช้! Internal Table
Exercise III
Tables Relationship for User Master
USR02-BNAME USR02-TRDAT ADCP-TEL_NUMBER
USR02
USR21
ADCP
BNAME
PERSNUMBER
ADDRNUMBER
Tables Relationship
Exercise III : User Master
usr02-bname
usr02-trdat
adcp-tel_number
ใช้! Internal Table
top related