Monday, 17 November 2014

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





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.

2 comments:

  1. After reading this post I got an idea about on this note.Really something grate in this article ,Thanks for sharing this. We are providing SAP courses training online. After reading this slightly am changed my way of introduction about my training to people. To know more Visit Us SAP PM Online Training Course

    ReplyDelete