SUBQUERIES EXAMPLES:

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

Sales order HEADER DATA

·        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

·        1^st 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).