07-28-2007 11:20 AM
Hi
experts .i want to know about read table and group by ,order by statement and its use.please help me with examples.
07-28-2007 11:39 AM
<b>REAB TABLE</b> reads only 1 record at a time
these are the results for SY-SUBRC checks after read table
SY-SUBRC = 0:
An entry was read.
SY-TABIX is set to the index of the entry.
SY-SUBRC = 2:
An entry was read.
SY-TABIX is set to the index of the entry. This return code can only occur when you use the COMPARING addition. For further detauls, refer to the COMPARING section of the additions
SY-SUBRC = 4:
No entry was read.
The value of SY-TABIX depends on the table type and whether the BINARY SEARCH addition was specified.
If the table is a SORTED TABLE or a table sorted in ascending order of the type STANDARD TABLE with the BINARY SEARCH addition, SY-TABIX refers to the next-highest index.
Otherwise, SY-TABIX is undefined.
SY-SUBRC = 8:
No entry was read.
This return code only occurs with a SORTED TABLE or a STANDARD TABLE with the BINARY SEARCH addition. SY-TABIX is set to the number of all entries plus 1.
EX: READ TABLE it_tab WITH TABLE KEY matnr = 'mat001'.
<b>Reward Points If Useful...</b>
07-28-2007 11:35 AM
Hi
Group By and Order By is not used now-a-days instead we SORT the itab by that field and use the control break statements lik AT NEW, AT END OF .
see the sample report and understand the read
REPORT zm_shelf_life_list
NO STANDARD PAGE HEADING
MESSAGE-ID zm_msg
LINE-SIZE 124
LINE-COUNT 60(1).
************************************************************************
Purpose : This report displays the list of Materials Shelf Life
based on the Values entered on Selection Screen.
*
************************************************************************
T A B L E S D E C L A R A T I O N S
************************************************************************
TABLES: mara, " Material Master
t001w, " Plants
t001l. " Storage Locations
************************************************************************
T Y P E S D E C L A R A T I O N S
************************************************************************
Material Documents Main Structure
TYPES: BEGIN OF s_mat,
mblnr TYPE mblnr, " GI Doc No
mjahr TYPE mjahr, " Fiscal Year
zeile TYPE mblpo, " Item No
bwart TYPE bwart, " Movement Type
matnr TYPE matnr, " Material No
werks TYPE werks_d, " Plant
lgort TYPE lgort_d, " St.Location
menge TYPE menge_d, " Quantity
meins TYPE meins, " UOM
hsdat TYPE hsdat, " Production date
vfdat TYPE vfdat, " Shelf Exp.Date
END OF s_mat.
Material data Structure
TYPES: BEGIN OF s_mat1,
matnr TYPE matnr, " Material No
bwart TYPE bwart, " Movement Type
menge TYPE menge_d, " Quantity
meins TYPE meins, " UOM
werks TYPE werks_d, " Plant
lgort TYPE lgort_d, " St.Location
hsdat TYPE hsdat, " Production date
vfdat TYPE vfdat, " Shelf Exp.Date
END OF s_mat1.
Material Text Details
TYPES: BEGIN OF s_mt,
matnr TYPE matnr, " Material No
maktx TYPE maktx, " Material Text
END OF s_mt.
Material Qty Structure
TYPES: BEGIN OF s_rep1,
matnr TYPE matnr, " Material No
menge TYPE menge_d, " Quantity 105
menge1 TYPE menge_d, " Quantity 201
END OF s_rep1.
Output Main Structure
TYPES: BEGIN OF s_rep,
matnr TYPE matnr, " Material No
bwart TYPE bwart, " Movement Type
menge TYPE menge_d, " Quantity
maktx TYPE maktx, " Plant
werks TYPE werks_d, " Plant
lgort TYPE lgort_d, " St.Location
meins TYPE meins, " UOM
hsdat TYPE hsdat, " Production date
vfdat TYPE vfdat, " Shelf Exp.Date
rsl TYPE i, " RSL
END OF s_rep.
************************************************************************
D A T A D E C L A R A T I O N S
************************************************************************
DATA: gv_title1 TYPE sylisel, " Report title
gv_dial, " Color flag
gv_year(4) TYPE n, " Fiscal Year
gv_date TYPE sy-datum, " Date
gv_days TYPE i, " Days
gv_menge TYPE menge_d. " Quantity
************************************************************************
C O N S T A N T S D E C L A R A T I O N S
************************************************************************
CONSTANTS: c_x TYPE c VALUE 'X', " Flag
c_105 TYPE bwart VALUE '105', " Movement Type
c_201 TYPE bwart VALUE '201', " Movement Type
c_261 TYPE bwart VALUE '261', " Movement Type
c_202 TYPE bwart VALUE '202', " Movement Type
c_262 TYPE bwart VALUE '262', " Movement Type
c_641 TYPE bwart VALUE '641', " Movement Type
c_643 TYPE bwart VALUE '643', " Movement Type
c_642 TYPE bwart VALUE '642', " Movement Type
c_106 TYPE bwart VALUE '106', " Movement Type
c_644 TYPE bwart VALUE '644', " Movement Type
c_645 TYPE bwart VALUE '645', " Movement Type
c_646 TYPE bwart VALUE '646', " Movement Type
c_661 TYPE bwart VALUE '661', " Movement Type
c_662 TYPE bwart VALUE '662', " Movement Type
c_541 TYPE bwart VALUE '541', " Movement Type
c_542 TYPE bwart VALUE '542'. " Movement Type
************************************************************************
I N T E R N A L T A B L E S D E C L A R A T I O N S
************************************************************************
DATA: i_mat TYPE STANDARD TABLE OF s_mat WITH HEADER LINE,
i_mat1 TYPE STANDARD TABLE OF s_mat1 WITH HEADER LINE,
i_mt TYPE STANDARD TABLE OF s_mt WITH HEADER LINE,
i_rep TYPE STANDARD TABLE OF s_rep WITH HEADER LINE,
i_rep1 TYPE STANDARD TABLE OF s_rep1 WITH HEADER LINE.
************************************************************************
S E L E C T I O N S C R E E N
************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_matnr FOR mara-matnr, " Material No
s_werks FOR t001w-werks, " Plant
s_lgort FOR t001l-lgort. " Storage Location
SELECTION-SCREEN END OF BLOCK b1.
************************************************************************
I N I T I A L I Z A T I O N *
************************************************************************
INITIALIZATION.
************************************************************************
A T S E L E C T I O N - S C R E E N *
************************************************************************
AT SELECTION-SCREEN.
Validate the screen fields
PERFORM validate_screen.
************************************************************************
S T A R T - O F - S E L E C T I O N *
************************************************************************
START-OF-SELECTION.
Fetch main data from MSEG Table
PERFORM fetch_data.
************************************************************************
T O P - O F - P A G E *
************************************************************************
TOP-OF-PAGE.
Header of the List
PERFORM header.
************************************************************************
E N D - O F - P A G E *
************************************************************************
Footer
END-OF-PAGE.
ULINE.
************************************************************************
E N D - O F - S E L E C T I O N *
************************************************************************
END-OF-SELECTION.
Display the Report Output data
PERFORM display_data.
&----
*& Form validate_screen
&----
Validation of Selection Screen fields
----
FORM validate_screen .
Validation of Material Number
CLEAR mara-matnr.
IF NOT s_matnr[] IS INITIAL.
SELECT matnr UP TO 1 ROWS
INTO mara-matnr
FROM mara
WHERE matnr IN s_matnr.
ENDSELECT.
IF sy-subrc <> 0.
MESSAGE e000 WITH 'Invalid Material Number'(002).
ENDIF.
ENDIF.
Validation of Plant
CLEAR t001w-werks.
IF NOT s_werks[] IS INITIAL.
SELECT werks UP TO 1 ROWS
INTO t001w-werks
FROM t001w
WHERE werks IN s_werks.
ENDSELECT.
IF sy-subrc <> 0.
MESSAGE e000 WITH 'Invalid Plant'(003).
ENDIF.
ENDIF.
Validation of Storage Location
CLEAR t001l-lgort.
IF NOT s_lgort[] IS INITIAL.
SELECT lgort UP TO 1 ROWS
INTO t001l-lgort
FROM t001l
WHERE lgort IN s_lgort.
ENDSELECT.
IF sy-subrc <> 0.
MESSAGE e000 WITH 'Invalid Storage Location'(004).
ENDIF.
ENDIF.
ENDFORM. " validate_screen
&----
*& Form fetch_data
&----
Fetching the data from Database Table MSEG
----
FORM fetch_data .
CLEAR : gv_year, gv_date, gv_days.
gv_date = sy-datum.
gv_year = gv_date+0(4).
CLEAR i_mat.
REFRESH i_mat.
SELECT a~mblnr " GI Doc No
a~mjahr " Fiscal Year
b~zeile " Item No
b~bwart " Movement Type
b~matnr " Material No
b~werks " Plant
b~lgort " St.Location
b~menge " Quantity
b~meins " UoM
b~hsdat " Production date
b~vfdat " Shelf Exp.Date
INTO TABLE i_mat
FROM mkpf AS a JOIN mseg AS b
ON amblnr = bmblnr JOIN mara AS c
ON bmatnr = cmatnr
WHERE a~mjahr EQ gv_year AND
b~matnr IN s_matnr AND
b~werks IN s_werks AND
b~lgort IN s_lgort AND
b~bwart IN (c_105,c_201,c_261,c_642,c_644,
c_645,c_646,c_661,c_202,c_262,c_106,c_641,
c_541,c_542, c_662) AND
c~mhdhb <> ' '.
SORT i_mat BY mblnr mjahr zeile matnr.
Move the data to Materials Int Table
LOOP AT i_mat.
MOVE-CORRESPONDING i_mat TO i_mat1.
APPEND i_mat1.
CLEAR i_mat1.
ENDLOOP.
SORT i_mat1 BY matnr bwart.
IF NOT i_mat[] IS INITIAL.
Get the Material Text
SELECT matnr " Material No
maktx " Material Description
INTO TABLE i_mt
FROM makt
FOR ALL ENTRIES IN i_mat
WHERE matnr = i_mat-matnr AND
spras = sy-langu.
ENDIF.
SORT i_mt BY matnr.
DELETE ADJACENT DUPLICATES FROM i_rep COMPARING matnr.
Move the data to i_rep int.table
LOOP AT i_mat1.
AT END OF bwart.
READ TABLE i_mat1 INDEX sy-tabix.
SUM.
i_rep-menge = i_mat1-menge.
i_rep-meins = i_mat1-meins.
i_rep-hsdat = i_mat1-hsdat.
i_rep-vfdat = i_mat1-vfdat.
i_rep-matnr = i_mat1-matnr.
i_rep-werks = i_mat1-werks.
i_rep-lgort = i_mat1-lgort.
i_rep-bwart = i_mat1-bwart.
CLEAR i_mt.
READ TABLE i_mt WITH KEY matnr = i_mat1-matnr.
IF sy-subrc = 0.
i_rep-maktx = i_mt-maktx.
ENDIF.
IF NOT i_mat1-vfdat IS INITIAL.
i_rep-rsl = i_mat1-vfdat - gv_date.
ENDIF.
APPEND i_rep.
CLEAR i_rep.
ENDAT.
ENDLOOP.
SORT i_rep BY matnr.
Move the Qty data to i_rep1 int.table
LOOP AT i_rep.
i_rep1-matnr = i_rep-matnr.
IF i_rep-bwart = c_105.
i_rep1-menge = i_rep-menge.
ELSEIF i_rep-bwart = c_106.
i_rep1-menge = i_rep1-menge - i_rep-menge.
ELSEIF ( i_rep-bwart = c_643 OR i_rep-bwart = c_201 OR
i_rep-bwart = c_261 OR i_rep-bwart = c_641 OR
i_rep-bwart = c_645 OR i_rep-bwart = c_661 OR
i_rep-bwart = c_541 ).
i_rep1-menge1 = i_rep1-menge1 + i_rep-menge.
ELSEIF ( i_rep-bwart = c_202 OR i_rep-bwart = c_262 OR
i_rep-bwart = c_642 OR i_rep-bwart = c_644 OR
i_rep-bwart = c_646 OR i_rep-bwart = c_662 OR
i_rep-bwart = c_542 ).
i_rep1-menge1 = i_rep1-menge1 - i_rep-menge.
ENDIF.
COLLECT i_rep1.
CLEAR i_rep1.
ENDLOOP.
SORT i_rep1 BY matnr.
ENDFORM. " fetch_data
&----
*& Form display_data
&----
display the Output Report
----
FORM display_data .
DATA: lv_flag. " New Flag
IF i_rep1[] IS INITIAL.
MESSAGE i000 WITH 'No data found'(014).
ELSE.
LOOP AT i_rep1.
Toggle Color
PERFORM toggle_color.
IF lv_flag <> space.
NEW-LINE.
ENDIF.
CLEAR gv_menge.
gv_menge = i_rep1-menge - i_rep1-menge1.
WRITE:/1 sy-vline,2(18) i_rep1-matnr.
lv_flag = c_x.
CLEAR i_rep.
READ TABLE i_rep WITH KEY matnr = i_rep1-matnr
bwart = c_105.
WRITE: 20 sy-vline,21(40) i_rep-maktx,
61 sy-vline,62(5) i_rep-werks,
67 sy-vline,68(7) i_rep-lgort,
75 sy-vline,76(10) i_rep-hsdat,
86 sy-vline,87(14) i_rep-vfdat,
101 sy-vline,102(4) i_rep-rsl,
106 sy-vline,107(13) gv_menge UNIT i_rep-meins,
120 sy-vline,121(3) i_rep-meins,
124 sy-vline.
ENDLOOP.
ULINE.
ENDIF.
ENDFORM. " display_data
&----
*& Form header
&----
Write the Report Header
----
FORM header .
FORMAT RESET.
gv_title1 = sy-title. " Set List Header
Standard header
CALL FUNCTION 'Z_STANDARD_HEADER'
EXPORTING
title1 = gv_title1.
FORMAT COLOR COL_HEADING.
ULINE.
WRITE:/1 sy-vline,2(18) 'Material No'(005) CENTERED,
20 sy-vline,21(40) 'Material Description'(006) CENTERED,
61 sy-vline,62(5) 'Plant'(007),
67 sy-vline,68(7) 'St.Locn'(008),
75 sy-vline,76(10) 'Prodn.Date'(009),
86 sy-vline,87(14) 'Shelf Exp.Date'(010),
101 sy-vline,102(4) 'RSL'(011),
106 sy-vline,107(13) 'Quantity'(012) CENTERED,
120 sy-vline,121(3) 'UoM'(013),
124 sy-vline.
ULINE.
ENDFORM. " header
&----
*& Form toggle_color
&----
This routine alters the color of the records in the list
----
FORM toggle_color.
IF gv_dial = space.
FORMAT COLOR COL_NORMAL INTENSIFIED OFF.
gv_dial = c_x.
ELSE.
FORMAT COLOR 1 INTENSIFIED OFF.
CLEAR gv_dial.
ENDIF.
ENDFORM. " toggle_color
<b>Reward points for useful Answers</b>
Regards
Anji
07-28-2007 11:51 AM
07-28-2007 11:39 AM
<b>REAB TABLE</b> reads only 1 record at a time
these are the results for SY-SUBRC checks after read table
SY-SUBRC = 0:
An entry was read.
SY-TABIX is set to the index of the entry.
SY-SUBRC = 2:
An entry was read.
SY-TABIX is set to the index of the entry. This return code can only occur when you use the COMPARING addition. For further detauls, refer to the COMPARING section of the additions
SY-SUBRC = 4:
No entry was read.
The value of SY-TABIX depends on the table type and whether the BINARY SEARCH addition was specified.
If the table is a SORTED TABLE or a table sorted in ascending order of the type STANDARD TABLE with the BINARY SEARCH addition, SY-TABIX refers to the next-highest index.
Otherwise, SY-TABIX is undefined.
SY-SUBRC = 8:
No entry was read.
This return code only occurs with a SORTED TABLE or a STANDARD TABLE with the BINARY SEARCH addition. SY-TABIX is set to the number of all entries plus 1.
EX: READ TABLE it_tab WITH TABLE KEY matnr = 'mat001'.
<b>Reward Points If Useful...</b>