SUBQUERIES EXAMPLES:
· SUB QUERIES are alternate for INNER JOIN. It allows to extract data from multiple tables.
System Fields:
Can be called as system variables also
Ø SAP provided once 171 system variables.
SY-SUBRC:
system variable for Error handling if it is equals to zero. There is no error , not equal to zero means error.
SP-ULINE:
it draws underline
SY-VLINE:
draw vertical line.
SAP GUI:
heights is 80 lines
width is 255 characters
DEMO ON SUBQUERIES
Ø Select SE 38, Enter Program: zsubqua_demo
Ø Define attributes
Ø Maintain attributes of Subqueries
Ø Save details
Requirements:
· In selection-screens, entering sales order no
· Entering sales order no i.e
|
· Output is sales order no entered ands displays item and materials having in each Sales Order
· Entering header data
· Displaying header data and item data
HEADER DATA ITEM DATA
SALES ORDER NO
|
ITEM
|
MATERIAL
|
NO STANDARD PAGE HEADING:
Is a syntax, suppress title, defined in attributes.
Line size 80:
Means from 255 characters width upto 80 characters width using for data display.
MESSAGE-ID:
Means message class, this is for Error-Handling. Transaction code is se91.
Ex: message-id zemdemo
Prog:
Ø Report zsubqua_demo NO STANDARD PAGE HEADING
LINE-SIZE 80
MESSAGE-ID ZEMDEMO.
Ø Click on zemdemo to create.
· It displays a dialogue box
Ø Select ‘YES’ to create
Ø Save
Ø Select ‘MESSAGES
Ø
· It displays
· Message class have message number
· This range starts from 000 and ends with 999
· Each number we can assign one error
· Total 1000 messages we can assign.
Ø Select message number 000 and assign error message:
Ex: 000 sales order num enter does not exists
Ø Save and back for f3
Ø Maintain tables work area. Which table we are going to use VBAK ,VBAP because there is no master table
TABLES: VBAK,
VBAP.
Ø Maintain selection screen logic. Am I entering one value or ranges values, we are entering one value so selection-screen logic is ‘parameters’.
PARAMETERS: SALES TYPE VBAK-VBELN.
Ø Declare internal table as per output screen, means how many fields? three(3) fields:
· Now I am going to introduce one more.. note down
TYPES: BEGIN OF FS,
· FS is a field string and it is a datatype
· How can u say it is a datatype, bcoz by seeing “TYPES’ keyword
· We have two options two referring fields, those are: (i)Fields From Table, and (ii)Fields From Data Elements
· If u refer field from tables means referring dependent object
Ex:
Data: begin of I_ITAB OCCURS 0,
VBELN TYPE KNA1-KUNNR,
POSNR TYPE KNA1-KUNNR,
MATNR TYPE KNA1-MATNR,
END OF I_ITAB.
· If u refer field from table means referring dependent object
· If u refer data element from table means referring Independent object. Whenever independent object refer, performance can be improved.
· Whenever Data Element referred in referring data type so type keyword or like keyword? type keyword right because referring data type. So keyword is ‘type’ keyword.
VBELN TYPE VBELN_VA, ”Data elements of VBELN.
POSNR TYPE POSNR_VA,
MATNR TYPE MATNR, “item
END OF FS.
· FS is Field String, FS is a Data type, and FS is to maintaining three fields.
· whenever data element referred so keyword is TYPE
· Declare Internal Table work area and Body with reference of Data type FS .
· Will you declare work area and body as a data object or data type? We have to declare work area as a Data Object because to internal table have to hold data
· To hold data memory required
· Data object keep memory multiple records
DATA: WA TYPE FS. “ fs is referring data object.
· Here work area and field string are same bcoz both properties are same
Work area(WA)
|
=
|
Field string(FS)
|
· Now body it holds multiple records then it is a table format but not line format.
WORK AREA BODY
|
· Body is a table format. Now data object is body.
DATA: BODY TYPE TABLE OF FS.
· This internal table with reference of Field string.
· Real time scenario this is only useful
Ø SQL statement using Sub Query
Ø
SELECT VBELN POSNR MATNR FROM VBAP
INTO TABLE BODY[]
WHERE VBELN = SALES
AND EXISTS
( SELECT VBELN FROM VBAK ) àhere execution starts from here means “select 1000 sales order FROM Header Data table
· Make it I entered input sales order no 1000
· Always analyze subquery from last two i.e. select 1000 sales order from header data table
· If 1000 exists in header data table then extract its item data from item data table and keep that extracted data in body where clause as per selection screen(parameter).
· Initially system can check header data details
· If header data exists then it can start to extract related item data
· Now data is in Body.
Ø If data is not in body
IF SY-SUBRC NE 0. “In case of error.
Ø Means sales order no doesn’t exist
MESSAGE E000.
Ø
· MESSAGE is a syntax and E for Error. And message no 000
ELSE. “IF SALES ORDER EXISTS.
Ø logic to maintain column heading
WRITE:/ SY-ULINE(80).
· It keeps underline upto 80 characters.
WRITE: /1 SY-VLINE, 2 ‘SALES ORDER NO’,
40 SY-VLINE, 41 ‘ITEM’,
60 SY-VLINE, 61 ‘MATERIAL’,
80 SY-VLINE.
WRITE:/ SY-ULINE(80).
· From 255 character width we are using 80 character
· 1st character vertical line
·
Ø Processing logic to display output.
LOOP AT BODY[] INTO WA.
WRITE: /1 SY-VLINE, 2 WA-VBELN,
40 SY-VLINE, 41 WA-POSNR,
60 SY-VLINE, 61 WA-MATNR,
80 SY-VLINE.
END LOOP .
ENDIF.
WRITE: / SY-ULINE(80).
Ø After data printed release data from int. table
CLEAR WA.
REFRESH BODY[].
Ø Save and activate.
· Internal table have 3 syntax: 91)with header line (2) without headerline (3) with reference of field string(FS).