8/6/2019 CURS 4 - Structura Programului SELECT

1/28

8/6/2019 CURS 4 - Structura Programului SELECT

2/28

Program structure

8/6/2019 CURS 4 - Structura Programului SELECT

3/28

Program structureREPORT... -> Program startTYPES:-> Type declarationDATA:... -> Data declaration...

INITIALIZATION. -> Triggered in executable programs before the selection screen isdisplayed....SELECTION-SCREEN -> Parameters and select-options

AT SELECTION-SCREEN. -> Events for the selection screen...START-OF-SELECTION. -> Start of processing

... ->END-OF-SELECTION....FORM... -> Procedures...ENDFORM.

8/6/2019 CURS 4 - Structura Programului SELECT

4/28

SELECTION-SCREEN- Parametersy You use the PARAMETERS statement to declare variables, similarly to the

DATA statement. Variables declared with the PARAMETERS statement arecalled parameters. For each parameter declared, an input field appears on thecorresponding selection screen.

PARAMETERS

[()] [TYPE |LIKE ] [DECIMALS ].

y To assign a default value to a parameter, you use the following syntax:

PARAMETERS

...... DEFAULT ......

y To make a parameter obligatory :

PARAMETERS

...... OBLIGATORY.

8/6/2019 CURS 4 - Structura Programului SELECT

5/28

PARAMETERS- examplesPARAMETERS: p_int TYPE i OBLIGATORY.

PARAMETERS: p_val(10) TYPE c DEFAULT ABC.

PARAMETERS: p_date TYPE d DEFAULT sy-datum.PARAMETERS: p_matnr TYPE matnr.

START-OF-SELECTION.

WRITE: p_int, p_val, p_date.

8/6/2019 CURS 4 - Structura Programului SELECT

6/28

SELECTION-SCREEN Select-optionsy Used for complex selections from database (SELECT)

y Can have multiple values (single values or intervals)

y SELECT-OPTIONS for .

y can be a column of a database table, or

an internal field in the program. A selection table

is an internal table object of the standard table typethat has a standard key and a header line. Selectiontables are used to store complex selections using astandardized procedure.

8/6/2019 CURS 4 - Structura Programului SELECT

7/28

SELECTION-SCREEN Select-optionsy Checking Selection Criteria

Use the following logical expression to check whether

the contents of a field satisfy the criteria ina selection table:

... IN ....

8/6/2019 CURS 4 - Structura Programului SELECT

8/28

SELECTION-SCREEN Select-optionsy Example :

TABLES: mara.DATA v_matnr TYPE mara-matnr.

SELECT-OPTIONS s_matnr FOR mara-matnr.

START-OF-SELECTION.

v_matnr = 100-100.IF v_matnr IN s_matnr.

WRITE : Valoarea se afla in SELECT-OPTION.

ENDIF.

8/6/2019 CURS 4 - Structura Programului SELECT

9/28

SELECT-OPTIONS structureThe row type of a selection table is a structure that consists of the following four components:SIGN, OPTION, LOW and HIGH. Each row of a selection table that contains values represents asub-condition for the complete selection criterion. Description of the individual components: SIGNThe data type of SIGN is C with length 1. The contents of SIGN determine for each row

whether the result of the row condition is to be included in or excluded from the resultingset of all rows. Possible values are I and E.

I stands for inclusive (inclusion criterion - operators are not inverted) E stands for exclusive (exclusion criterion - operators are inverted)

OPTIONThe data type of OPTION is C with length 2. OPTION contains the selection operator.The following operators are available: If HIGH is empty, you can use EQ, NE, GT, LE, LT,CP, and NP. These operators are the

same as those that are used for logical expressions. Yet operators CP andNP do not have the full functional scope they have in normal logical expressions. Theyare only allowed if wildcards ( '*' or '+' ) are used in the input fields, and no escapecharacter is defined. If HIGH is filled, you can use BT (BeTween) and NB (Not Between). These operatorscorrespond to BETWEEN and NOT BETWEEN that you use when you check if a fieldbelongs to a range

8/6/2019 CURS 4 - Structura Programului SELECT

10/28

SELECT-OPTIONS structure LOWThe data type of LOW is the same as the column type of the database table, to which theselection criterion is linked. If HIGH is empty, the contents of LOW define a single field comparison. In combination

with the operator in OPTION, it specifies a condition for the database selection. If HIGH is filled, the contents of LOW and HIGH specify the upper and lower limits for arange. In combination with the operator in OPTION, the range specifies a condition forthe database selection.

HIGHThe data type of HIGH is the same as the column type of the database table, to which

the selection criterion is linked. The contents of HIGH specify the upper limit for a range

selection.

8/6/2019 CURS 4 - Structura Programului SELECT

11/28

8/6/2019 CURS 4 - Structura Programului SELECT

12/28

SELECT

8/6/2019 CURS 4 - Structura Programului SELECT

13/28

SELECT Processing Single Records

8/6/2019 CURS 4 - Structura Programului SELECT

14/28

Processing Single Recordsy Example:

DATA: wa_scarr TYPE scarr.

PARAMETERS: p_carrid TYPE s_carr_id.

START-OF-SELECTION.

SELECT SINGLE * INTO wa_scarr FROM scarr

WHERE carrid = p_carrid.

WRITE : wa_scarr-carrid, wa_scarr-carrname.

8/6/2019 CURS 4 - Structura Programului SELECT

15/28

Select Loops

8/6/2019 CURS 4 - Structura Programului SELECT

16/28

Select Loopsy Example:

TABLES: scarr.

DATA: wa_scarr TYPE scarr.SELECT-OPTIONS: s_carrid FOR scarr-carrid.

START-OF-SELECTION.

SELECT * INTO wa_scarr FROM scarrWHERE carrid IN s_carrid.

WRITE : / wa_scarr-carrid, wa_scarr-carrname.

ENDSELECT.

8/6/2019 CURS 4 - Structura Programului SELECT

17/28

Array Fetch

8/6/2019 CURS 4 - Structura Programului SELECT

18/28

Array FetchTABLES: scarr.DATA: gt_scarr TYPE TABLE OF scarr,

wa_scarr TYPE scarr.

SELECT-OPTIONS: s_carrid FOR scarr-carrid.

START-OF-SELECTION.SELECT * INTO TABLE gt_scarr FROM scarr

WHERE carrid IN s_carrid.LOOP AT gt_scarr INTO wa_scarr.WRITE : / wa_scarr-carrid, wa_scarr-carrname.

ENDLOOP.

8/6/2019 CURS 4 - Structura Programului SELECT

19/28

INTO clause

8/6/2019 CURS 4 - Structura Programului SELECT

20/28

INTO clauseTYPES: BEGIN OF ty_scarr,

carrid TYPE s_carr_id,carrname TYPE s_carrname,

END OF ty_scarr.DATA: wa_scarr TYPE ty_scarr.PARAMETERS: p_carrid TYPE s_carr_id.

START-OF-SELECTION.SELECT SINGLE carrid carrname INTO wa_scarrFROM scarr WHERE carrid = p_carrid.

WRITE : wa_scarr-carrid, wa_scarr-carrname.

8/6/2019 CURS 4 - Structura Programului SELECT

21/28

INTO clause

8/6/2019 CURS 4 - Structura Programului SELECT

22/28

INTO clauseDATA: wa_scarr TYPE scarr.

PARAMETERS: p_carrid TYPE s_carr_id.

START-OF-SELECTION.

SELECT SINGLE carrid carrname INTOCORRESPONDING FIELDS OFwa_scarr

FROM scarr WHERE carrid = p_carrid.

WRITE : wa_scarr-carrid, wa_scarr-carrname.

8/6/2019 CURS 4 - Structura Programului SELECT

23/28

INTO clauseDATA: gv_carrid TYPE s_carr_id,

gv_carrname TYPE s_carrname.

PARAMETERS: p_carrid TYPE s_carr_id.

START-OF-SELECTION.

SELECT SINGLE carrid carrname

INTO (gv_carrid, gv_carrname)

FROM scarr WHERE carrid = p_carrid.

WRITE : gv_carrid, gv_carrname.

8/6/2019 CURS 4 - Structura Programului SELECT

24/28

JOIN

8/6/2019 CURS 4 - Structura Programului SELECT

25/28

[INNER]JOINTable 1 Table 2

|----|----|----|----| |----|----|----|----|----|| A | B | C | D | | D | E | F | G | H ||----|----|----|----| |----|----|----|----|----|| a1 | b1 | c1 | 1 | | 1 | e1 | f1 | g1 | h1 || a2 | b2 | c2 | 1 | | 3 | e2 | f2 | g2 | h2 |

| a3 | b3 | c3 | 2 | | 4 | e3 | f3 | g3 | h3 || a4 | b4 | c4 | 3 | |----|----|----|----|----||----|----|----|----|

\ /\ /\ /\ /\/

Inner Join

|----|----|----|----|----|----|----|----|----|| A | B | C | D | D | E | F | G | H ||----|----|----|----|----|----|----|----|----|| a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 || a2 | b2 | c2 | 1 | 1 | e1 | f1 | g1 | h1 || a4 | b4 | c4 | 3 | 3 | e2 | f2 | g2 | h2 ||----|----|----|----|----|----|----|----|----|

8/6/2019 CURS 4 - Structura Programului SELECT

26/28

JOIN exampleTYPES: BEGIN of ty_str,

matnrTYPE matnr,maktx TYPE maktx,

END OF ty_str.TABLES: mara.

DATA: gt_matnr TYPE TABLE OF ty_str,gs_matnr TYPE ty_str.

SELEC T-OPTIONS s_matnr FOR mara-matnr.

START-OF-SELECTION.SELECT mara~matnr makt~maktx

INTO TABLE gt_matnrFROM mara

JOIN makt ON mara~matnr = makt~matnrWHERE mara~matnr IN s_matnr.

LOOP AT gt_matnr INTO gs_matnr.WRITE: / Material: , gs_matnr-matnr, Descriere: , gs_matnr-maktx.

ENDLOOP.

8/6/2019 CURS 4 - Structura Programului SELECT

27/28

LEFT[OUTER]JOINTabelle 1 Tabelle 2

|----|----|----|----| |----|----|----|----|----|| A | B | C | D | | D | E | F | G | H ||----|----|----|----| |----|----|----|----|----|| a1 | b1 | c1 | 1 | | 1 | e1 | f1 | g1 | h1 || a2 | b2 | c2 | 1 | | 3 | e2 | f2 | g2 | h2 |

| a3 | b3 | c3 | 2 | | 4 | e3 | f3 | g3 | h3 || a4 | b4 | c4 | 3 | |----|----|----|----|----||----|----|----|----|

\ /\ /\ /\ /\/

Left Outer Join

|----|----|----|----|----|----|----|----|----|| A | B | C | D | D | E | F | G | H ||----|----|----|----|----|----|----|----|----|| a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 || a2 | b2 | c2 | 1 | 1 | e1 | f1 | g1 | h1 || a3 | b3 | c3 | 2 |NULL|NULL|NULL|NULL|NULL|| a4 | b4 | c4 | 3 | 3 | e2 | f2 | g2 | h2 ||----|----|----|----|----|----|----|----|----|

8/6/2019 CURS 4 - Structura Programului SELECT

28/28

SELECT- exerciseSa se afiseze pe ecran urmatoarele campuri:

- Cod material (MARA-MATNR)

- Greutate bruta (MARA-BRGEW)- Greutate neta (MARA-NTGEW)

- Descrierea materialului in engleza (MAKT-MAKTX)

Pentru materialele care respecta urmatoarele criterii deselectie :

- Cod material (MARA-MATNR) selectie multipla

- Grupa materiale (MARA-MATKL) - parametru