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).

CONTROL BREAKS

O/P IS

o/p is

o/p is

Add caption

1)      TICKET: (REQUIREMENT)IS

We have to display like below

*&---------------------------------------------------------------------*

*& Report  ZASSIGNMNT_CONTROLBREAK

*&

*&---------------------------------------------------------------------*

*&

*&

*&---------------------------------------------------------------------*

REPORT  ZASSIGNMNT_CONTROLBREAK.

*maintain table work area.

tables: vbrk,      "Billing Document: Header Data

        vbrp.      "bill doc.item table

*maintain selection-screen logic.

parameters: kunag type vbrk-kunag default '1000'.  "CUSTOMER NO

select-options: vbeln for vbrk-vbeln. "BILL DOC. NO

*SAVE AND GOTO FROM MENU BAR

*CHOOSE TEXT-ELEMENTS

*CLICK ON SELECT-TEXTS

* THEN U WILL GET LIKE THIS

*SAVE AND ACTIVATE.

*declare int. table as per o/p display WITH REF OF FIELD STRING

types:begin of ty_output,

      KUNAG TYPE KUNAG,     "VBRK- CUSTOMER NO.

      VBELN TYPE VBELN_VF,  "VBRK- BILL DOC ORDER NO

      posnr type posnr_vf,  "vbrp- table for item.

      matnr type matnr,     "vbrp- table FOR MATERIAL

      NETWR TYPE NETWR_FP,  "VBRP- TABLE PRICE

      WAERK TYPE WAERK,     "VBRK CURRENCY

      END OF TY_OUTPUT.

*DECLARE INT TABLE WORK AREA AND BODY WITH REFERENCY OF FIELD STRING i.e ty_output.

DATA: WA TYPE ty_output,

      itab like table of wa.

*open sql statement

SELECT VBRK~VBELN

       VBRK~WAERK

       VBRP~POSNR

       VBRP~MATNR

       VBRP~NETWR

*EXTRACTED DATA FROM TABLES VBRP,VBRK from Database Server

       INTO

       TABLE ITAB[]        "EXTRACTED DATA STORED INTO BODY ITAB[]

*providing inner join between VBRP & VBRK

         FROM

           VBRP

             INNER JOIN

               VBRK

               ON

               VBRK~VBELN = VBRP~VBELN

*because of selection-screen option parameters WHERE clause should be maintained

WHERE VBRK~KUNAG = KUNAG "CUSTOMER NO

*BCOZ OF SELECT-OPTIONS 'IN' CLAUSE SHOULD BE MAINTAINED

AND

VBRK~VBELN IN VBELN.   "BILL DOC.NO.

*now all the data extracted from DB SERVER and stored into body ITAB[]

*so data is in body so we have to transfer data from body to wa.

*SORT THE DATA IN BODY

IF NOT ITAB[] IS INITIAL.

SORT ITAB[] BY VBELN.

*TRANSFER THE DATA FROM BODY TO WORK AREA.

CLEAR WA. "CLEAR THE INT.TABLE WA.

LOOP AT ITAB INTO WA.

*APPLY FIRST CONTROL BREAK i.e. AT FIRST.

AT FIRST.

WRITE: / 'BILL DOCUMENT LIST'.

*PROVIDE AT WITH ENDAT.

ENDAT.

*PROVIDE 2ND CONTROL BREAK i.e at new.

AT NEW VBELN. "BILL DOC.NO.

   WRITE:/ 'BILL DOCUMENT NO:', WA-VBELN.

*  LOGIC TO MAINTAIN COLUMN HEADINGS

   write:/ sy-uline(70).

   WRITE:/1 SY-VLINE, 2 'ITEMS',

        10 SY-VLINE, 11 'MATERIAL',

        30 SY-VLINE, 31 'PRICE',

        50 SY-VLINE, 51 'CURRENCY',

        60 SY-VLINE.

   WRITE:/ SY-ULINE(70).

ENDAT.

*logic to display bill document lists under respective column headings.

    WRITE:/1 SY-VLINE, 2 WA-POSNR,

           10 SY-VLINE, 11 WA-MATNR,

           30 SY-VLINE, 31 WA-NETWR,

           50 SY-VLINE, 51 WA-WAERK,

           60 SY-VLINE.

    WRITE:/ SY-ULINE(70).

*APPLLY THIRD CONTROL BREAK AT ENDOF.

AT END OF NETWR.

*   NETWR.

   SUM.

     WRITE:/ 'TOTAL AMOUNT:', WA-NETWR.

ENDAT.

AT LAST.

 WRITE:/ 'GRAND TOTAL:', WA-NETWR.

 SUM.

 ENDAT.

*LOOP WITH ENDLOOP

ENDLOOP.

*IF WITH ENDIF.

ENDIF.