OPEN SQL in ABAP

 

OPEN SQL commands that are used on database tables are

  1. SELECT
  2. INSERT
  3. UPDATE
  4. MODIFY
  5. DELETE
  6. COMMIT WORK

SELECT is used for getting records from dbtable.

  • SELECT....

ENDSELECT.

  • SELECT....  FROM TABLE INTO ITAB.

INSERT is for adding new records to dbtable.

  • INSERT DBTAB FROM WA.
  • INSERT INTO DBTAB VALUES WA.

workarea records  will be transferred to dbtable.

  • INSERT DBTAB FROM TABLE ITAB.

after the execution of this

SY-DBCNT contains no.  of records added

SY-SUBRC is 0 if operation is successful

is 4 if operation is failed

UPDATE is for changing the contents of existing records.

  • UPDATE DBTAB SET FLDI = VAL1 FLD 2 = VAL2....

all records field values will be modified.

Use where clause to process few records.

Ex.  UPDATE SPFLI SET CARRID = 'AN'WHERE CARRID = 'AP'.

UPDATE DBTAB FROM WA.

the record which is matched with workarea record according to its PRIMARY KEY fields will be modified

  • UPDATE DBTAB FROM TABLE ITAB.

SY-DBCNT & SY-SUBRC will be set accordingly

sy-subrc is 0 if atleast one record is updated.

MODIFY is the combination of INSERT and UPDATE commands

  • MODIFY DBTAB FROM WA.
  • MODIFY DBTAB FROM TABLE ITAB.

if the record is already existed it will be UPDATED.

if the record is not there it will be INSERTED

SY-DBCNT & SY-SUBRC will be set accordingly.

COMMIT WORK is used to make above operations permanent.

ROLLBACK WORK is used to cancel the above operations.

These are used in module pool programming.

Do not modify the records of standard tables of database server through simple programs.

They must always be updated by standard transactions only.

Comments

Post a Comment

Popular posts from this blog

Domains and Data Elements

  Domains A domain provided Technical attributes (type, length and possible values) for a field. The definition of domain in ABAP is same as that in Mathematics. In the world of Mathematics, a domain is defined as the set of input values that are valid for a given function. The same holds true for domains in ABAP. To understand this, let us first see what is the role of domains in ABAP data dictionary. Domains -> Data Elements -> (Structures, DB tables, program references etc.)
Read more

Sample ABAP program for Updating notepad file data to Internal table and Sending it to Application server

 *--Structure type TYPES: BEGIN OF TY_STD, RONO TYPE C LENGTH 4, NAME1 TYPE C LENGTH 20, GEN TYPE C LENGTH 6, FEES TYPE C LENGTH 10. END OF TY_STD. *--Header line and internal table DATA WKA TYPE TY_STD. DATA INT_TAB TYPE TABLE OF TY_STD. *--Application server files are known as "Datasets" *--here "fname" is the Dataset DATA FNAME TYPE C LENGTH 40 VALUE "StudentsInfo.txt". *--Convert notepad file data into internal table CALL FUNCTION 'GUI_UPLOAD' EXPORTING FILENAME = 'C:Documents and SettingsAdministratorDesktopstudents.txt' HAS FIELD_SEPARATOR = 'X' TABLES DATA_TAB = INT_TAB. *--Sending data to Application Server OPEN DATASET FNAME FOR OUTPUT IN TEXT MODE ENCODING DEFAULT. LOOP AT INT_TAB INTO WKA. TRANSFER WKA TO FNAME. ENDLOOP. CLOSE DATASET FNAME.
Read more

OPEN SQL EXAMPLES IN ABAP – PART 2

 ******************************************* **displaying Raw Materials and Finished products **only from MARA table. DATA WA TYPE MARA. * **--WHERE clause is for filtering the records SELECT * FROM MARA INTO WA WHERE MTART = 'ROH' OR MTART = 'FERT'. WRITE: / WA-MATNR, WA-MBRSH, WA-MTART, WA-MEINS. ENDSELECT. * **************************************************
Read more