*&---------------------------------------------------------------------*
*& Report ZABAP_SQL
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZABAP_SQL no standard page heading.
******************************************************
*********** SQL Statement - Insert (using WA)
******************************************************
*data : wa type zkna5.
*
*WA-CUSTOMER = '1006'.
*WA-CNAME = 'Ramachandran'.
*WA-CITY = 'Salem'.
*WA-COUNTRY = 'IN'.
*WA-REGION = 'SOUTH'.
*WA-INCOME = '558000'.
*WA-CURRENCY = 'INR'.
*
*INSERT ZKNA5 FROM WA.
*
*
*WA-CUSTOMER = '1007'.
*WA-CNAME = 'Srinivas Naidu'.
*WA-CITY = 'Anantapur'.
*WA-COUNTRY = 'IN'.
*WA-INCOME = '558000'.
*WA-CURRENCY = 'INR'.
*
*INSERT ZKNA5 FROM WA.
*
*IF SY-SUBRC = 0.
* Write : / 'record inserted successfully' color 4.
* else.
* Write : / 'Operation Failed' color 6.
* endif.
*******************************************************
************ SQL Statement - update (using WA)
*******************************************************
*
*data : wa type zkna5.
*
*WA-CUSTOMER = '1008'.
*WA-CNAME = 'Manivasagan'.
*WA-CITY = 'Chennai'.
*WA-COUNTRY = 'IN'.
*WA-REGION = 'SOUTH'.
*WA-INCOME = '900000'.
*WA-CURRENCY = 'INR'.
*
*Update ZKNA5 FROM WA.
*
*IF SY-SUBRC = 0.
* Write : / 'record inserted successfully' color 4.
* else.
* Write : / 'Operation Failed' color 6.
* endif.
*Update ZKNA5 set region = 'SOU' where CUSTOMER BETWEEN '1005' AND '1008'.
******************************************************
*********** SQL Statement - Modify (using WA)
******************************************************
*data : wa type zkna5.
*
*WA-CUSTOMER = '1008'.
*WA-CNAME = 'Manivasagan'.
*WA-CITY = 'Chennai'.
*WA-COUNTRY = 'IN'.
*WA-REGION = 'SOUTH'.
*WA-INCOME = '900000'.
*WA-CURRENCY = 'INR'.
*
*Update ZKNA5 FROM WA.
*
*IF SY-SUBRC = 0.
* Write : / 'record inserted successfully' color 4.
* else.
* Write : / 'Operation Failed' color 6.
* endif.
******************************************************
*********** SQL Statement - Delete USING WHERE Clause
******************************************************
*DELETE FROM ZKNA5 Where customer = '1008'.
*******************************************************
************ SQL Statement - Delete USING WHERE Clause
*******************************************************
*Data : wa type zkna5,
* itab type table of zkna5.
*
*DELETE FROM ZKNA5 Where city = 'Baroda'.
*
*select *
*from zkna5
*into table itab.
*
*loop at itab into wa.
* write : / wa-customer, wa-cname, wa-city, wa-income .
*endloop.
*skip 2.
*write: /5 sy-dbcnt, 'records retrieved' color 7.
*
*skip.
*uline.
*uline.
*
*ROLLBACK WORK.
*
*
*select *
*from zkna5
*into table itab.
*
*loop at itab into wa.
* write : / wa-customer, wa-cname, wa-city, wa-income.
*endloop.
*skip 2.
*write: /5 sy-dbcnt, 'records retrieved' color 5.
*******************************************************
************ SQL Statement - Delete WITH COMMIT
*******************************************************
*Data : wa type zkna5,
* itab type table of zkna5.
*
*DELETE FROM ZKNA5 Where city = 'Baroda'.
*
*select *
*from zkna5
*into table itab.
*
*loop at itab into wa.
* write : / wa-customer, wa-cname, wa-city, wa-income .
*endloop.
*skip 2.
*write: /5 sy-dbcnt, 'records retrieved' color 7.
*
*skip.
*uline.
*uline.
*
*COMMIT WORK.
*
*
*select *
*from zkna5
*into table itab.
*
*loop at itab into wa.
* write : / wa-customer, wa-cname, wa-city, wa-income.
*endloop.
*skip 2.
*write: /5 sy-dbcnt, 'records retrieved' color 5.
*******************************************************
************** Select - all fields into wa
*******************************************************
*
*data : wa type zkna5.
*
*
*select *
*from zkna5
*into wa.
*
*Write : / WA-CUSTOMER, WA-CITY, WA-INCOME.
*
*ENDSELECT.
*******************************************************
************** Select single - all fields into wa
*******************************************************
*
*data : wa type zkna5.
*
*
*select single *
*from zkna5
*into wa
*where region = 'SOU'.
*
*Write : / WA-CUSTOMER, WA-CITY, WA-INCOME.
******************************************************
************* Select single - specific fields
******************************************************
*
*data : wa type zkna5.
*
*
*select single customer city income
*from zkna5
*into corresponding fields of wa
*where region = 'SOU'.
*
*Write : / WA-CUSTOMER, WA-CITY, WA-INCOME.
******************************************************
************* single column internal table
******************************************************
*data : a type i,
* b type table of i.
*
*
*a = 2.
*append a to b.
*
*a = 32.
*append a to b.
*
*a = 8.
*append a to b.
*
*a = 109.
*append a to b.
*
*
*clear a.
*
*loop at b into a.
*write : / a color = sy-tabix.
*endloop.
******************************************************
************* Internal table with header line
******************************************************
*data : begin of itab occurs 0,
* CUSTOMER type zkna5-CUSTOMER,
* CNAME type zkna5-CNAME,
* CITY type zkna5-CITY,
* COUNTRY type zkna5-COUNTRY,
* REGION type zkna5-REGION,
* INCOME type zkna5-INCOME,
* CURRENCY type zkna5-CURRENCY,
* end of itab.
*
*
*
*write : /2 'Customer', 14 'City', 35 'Income'.
*
*select *
*from zkna5
*into table itab.
*
*loop at itab .
*write : / itab-customer, itab-city, itab-income.
*endloop.
******************************************************
************* Internal table using types
******************************************************
*
*types : begin of ty_kna5,
* CUSTOMER type zkna5-CUSTOMER,
* CNAME type zkna5-CNAME,
* CITY type zkna5-CITY,
* COUNTRY type zkna5-COUNTRY,
* REGION type zkna5-REGION,
* INCOME type zkna5-INCOME,
* CURRENCY type zkna5-CURRENCY,
* end of ty_kna5.
*
*
*data : wa type ty_kna5,
* itab type table of ty_kna5 ,
* itab2 type table of ty_kna5.
*
*
*select *
*from zkna5
*into table itab.
*
*loop at itab .
*write : / itab-customer, itab-city, itab-income.
*endloop.
*******************************************************
************** Internal table
*******************************************************
*
*Parameters : P_ort01 type kna1-ort01.
*
*data : wa type kna1,
* itab type table of kna1.
*
*DATA : CITY type kna1-ort01.
*
*city = p_ort01.
*translate city to upper case.
*
*select *
*from kna1
*into table itab
*where ort01 = p_ort01 or
*ort01 = city .
*
*
*loop at itab into wa.
*
*write: / wa-kunnr, wa-name1, wa-ort01.
*
*endloop.
*
*if sy-subrc = 0.
*write: / sy-dbcnt, 'records retrieved' color 6.
*endif.
*******************************************************
************** Internal table using structure
*******************************************************
*data : wa type zstr_kna5,
*itab1 type table of zstr_kna5,
*itab2 type table of zstr_kna5.
*
*
*select *
*from kna1
*into CORRESPONDING Fields of table itab1
*where ort01 like 'P%' .
*
*itab2[] = itab1[].
*
*clear itab1[].
**move itab1 to itab2.
*
*loop at itab2 into wa.
*
*write: / wa-kunnr, wa-name1, wa-ort01.
*
*endloop.
*
*if sy-subrc = 0.
*write: / sy-dbcnt, 'records retrieved' color 6.
*endif.
*******************************************************
************** Internal table usage
*******************************************************
Tables : vbrk.
types : begin of ty_vbrk,
kunag type vbrk-kunag,
ort01 type kna1-ort01,
vtweg type vbrk-vtweg,
spart type vbrk-spart,
netwr type vbrk-netwr,
waerk type vbrk-waerk,
end of ty_vbrk.
data : wa type ty_vbrk,
itab type table of ty_vbrk.
Data : city type kna1-ort01.
select-options s_cno for vbrk-kunag.
select kunag vtweg spart netwr waerk
from vbrk
into CORRESPONDING FIELDS OF TABLE itab
where kunag in s_cno .
loop at itab into wa.
select single ort01
from kna1
into city
where kunnr = wa-kunag .
wa-ort01 = city.
modify itab from wa.
endloop.
loop at itab into wa.
write : / wa-kunag, wa-ort01, wa-netwr.
endloop.
No comments:
Post a Comment