dynamic selection

[Former Member]

How to write a select statement, if the field names are to be given in the selection screen?

[Former Member]

hi,

select

empid

from zemployee_info into table itab

where empid in s_empid.

regards,

sreelakshmi

[Former Member]

Hi,

SELECT

Reads data from the database.

Syntax

SELECT <result>

INTO <target>

FROM <source>

[WHERE <condition>]

[GROUP BY <fields>]

[HAVING <cond>]

[ORDER BY <fields>].

The SELECT statement consists of a series of clauses, each of which fulfils a certain task:

SELECT clause

Defines the structure of the selection.

Syntax

SELECT [SINGLE]|[DISTINCT]

• | <si> [AS <a i>]... <agg>( [DISTINCT] <s j>) [AS <a j>]...

The selection can be one line, SINGLE, or several lines. You can eliminate duplicate lines using the DISTINCT addition. To select the entire line, use *, otherwise, you can specify individual columns <si>. For individual columns, you can use aggregate functions <agg>, and assign alternative column names <a i>.

INTO clause

Defines the target area into which the selection from the SELECT clause is written.

Syntax

... INTO [CORRESPONDING FIELDS OF] <wa>

| INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE <itab>

[PACKAGE SIZE <n>]

| INTO (<f1>, <f 2>,...)

The target area can be a flat work area <wa>, an internal table <itab>, or a list of fields <fi>. If you use the CORRESPONDING FIELDS addition, data is only selected if there is an identically-named field in the target area. If you use APPENDING instead of INTO, the data is appended to an internal table instead of overwriting the existing contents. PACKAGE SIZE allows you to overwrite or extend the internal table in a series of packages.The data type of the target area must be appropriate for the selection in the SELECT clause.

FROM clause

The FROM clause determines the database tables from which the data specified in the SELECT clause is read.

Syntax

... FROM [<tab> [INNER]|LEFT [OUTER] JOIN] <dbtab> [AS <alias>]

[ON <cond>]

[CLIENT SPECIFIED]

[BYPASSING BUFFER]

[UP TO <n> ROWS]

You can read both single fields and groups of fields. You link several tables using inner and outer joins to link tables with conditions <cond>, where <tab> is a single table or itself a join condition. The names of database tables may be specified statically or dynamically, and you can use alias names. You can bypass automatic client handling with the CLIENT SPECIFIED addition, and SAP buffering with BYPASSING BUFFER. You can also restrict the number of lines read from the table using the UP TO <n> ROWS addition.

WHERE clause

Restricts the number of lines selected.

Syntax

... [FOR ALL ENTRIES IN <itab>] WHERE <cond>

The condition <cond> may contain one or more comparisons, tests for belonging to intervals, value list checks, subqueries, selection table queries or null value checks, all linked with AND, OR, and NOT. If you use the FOR ALL ENTRIES addition, the condition <cond> is checked for each line of the internal table <itab> as long as <cond> contains a field of the internal table as an operand. For each line of the internal table, the system selects the lines from the database table that satisfy the condition. The result set is the union of the individual selections resulting from each line.

GROUP BY clause

Groups lines in the selection.

Syntax

... GROUP BY <s1> <s 2>

Groups lines with the same contents in the specified columns. Uses aggregate functions for all other columns in each group. All columns of the SELECT clause that are not listed in the GROUP BY clause must be included in aggregate functions.

HAVING clause

Restricts the number of line groups selected.

Syntax

... HAVING <cond>

Like the WHERE clause, but can only be used in conjunction with a GROUP BY clause. The HAVING clause uses conditions to restrict the number of groups selected.

ORDER BY clause

Sorts the lines of the selection.

Syntax

... ORDER BY PRIMARY KEY |... <si> [ASCENDING|DESCENDING]...

Sorts the selection in ascending or descending order according to the primary key or the contents of the fields listed.

Regards,

Priya.

[Former Member]

hi,

for parameters:

declaration:

selection-screen begin of block b1 with frame title t2.

parameters: empid like zemptab1-zempid3.

selection-screen end of block b1.

select * from zemptab1 into table wi_emp where zempid3 = empid.

Sample code:

tables zemptab1. "TABLES DECLARATION

**----

---

• DATA

**----

---

*

*DATA: var TYPE i,

• var1 LIKE zemptab1-salary.

*----

---

• SELECTION-SCREEN

*----

---

selection-screen begin of screen 045 title t1. " START OF SCREEN

selection-screen begin of block b1 with frame title t2.

parameters: empid like zemptab1-zempid3

matchcode object zempidsr.

selection-screen end of block b1.

skip 2.

selection-screen begin of block b2 with frame title t3.

parameters: rb1 radiobutton group one.

skip.

parameters: rb2 radiobutton group one.

selection-screen end of block b2.

skip 2.

selection-screen begin of block b3 with frame title t4.

selection-screen pushbutton 10(20) but1 user-command sub.

selection-screen pushbutton 40(20) but2 user-command res.

selection-screen pushbutton 70(20) but3 user-command exi.

selection-screen end of block b3.

selection-screen end of screen 045. " END OF SCREEN

*----

---

• INITIALIZATION

*----

---

initialization.

t1 = 'EMPLOYEE SALARY DETAILS'.

t2 = 'ENTER EMPLOYY ID'.

t3 = 'CHOOSE'.

t4 = 'SELECT'.

*----

---

• START-OF-SELECTION

*----

---

start-of-selection.

but1 = 'SUBMIT'.

but2 = 'RESET'.

but3 = 'EXIT'.

*----

---

• INTERNAL TABLE

*----

---

data wi_emp like zemptab1 occurs 0 with header line.

select * from zemptab1 into table wi_emp where zempid3 = empid.

call screen 045. "CALLING THE SCREEN 045

*----

---

• AT SELECTION-SCREEN

*----

---

at selection-screen.

if rb1 = 'X'.

perform sub1. "PERFORMING SUBROUTINE 2

elseif rb2 = 'X'.

perform sub2. "PERFORMING SUBROUTINE 1

endif.

*&----

---

*& CASE STATEMENTS

*&----

---

case sy-ucomm.

when 'SUB'.

leave to list-processing.

when 'RES'.

empid = ' '.

when 'EXI'.

leave program.

endcase.

&----

---

*& Form SUB1

&----

---

• text

----

---

form sub1.

data wi_emp like zemptab1 occurs 0 with header line.

select * from zemptab1 into table wi_emp where zempid3 = empid.

write: 'EMPLOYEE ID' intensified , ' EMPLOYEE NAME' intensified , ' DEPARTMENTID' INTENSIFIED , ' SALARY KEY' INTENSIFIED.

LOOP AT wi_emp.

WRITE: / wi_emp-zempid3 COLOR 1,

15 wi_emp-zename3 COLOR 6,

50 wi_emp-zedepid COLOR 4,

80 wi_emp-zsalkey COLOR 7.

WRITE 😕 'annual salary of'.

WRITE : wi_emp-zename3.

WRITE : ':'.

MULTIPLY wi_emp-salary BY 12.

WRITE : wi_emp-salary COLOR 2.

ENDLOOP.

ENDFORM. "SUB1

&----

---

*& Form SUB2

&----

---

• text

----

---

FORM sub2.

DATA wi_emp LIKE zemptab1 OCCURS 0 WITH HEADER LINE.

SELECT * FROM zemptab1 INTO TABLE wi_emp WHERE zempid3 = empid.

WRITE: 'employee id' INTENSIFIED , ' employee name' INTENSIFIED , ' DEPARTMENTID' INTENSIFIED , ' SALARY KEY' INTENSIFIED , ' SALARY' INTENSIFIED.

loop at wi_emp.

write: / wi_emp-zempid3 color 1,

15 wi_emp-zename3 color 2,

50 wi_emp-zedepid color 3,

80 wi_emp-zsalkey color 4,

100 wi_emp-salary color 5.

endloop.

endform.

hope this helps u,

Regards,

Arunsri

[matt]

Example:

PARAMETERS: field TYPE fieldname.

* Put some validation on DD03L to ensure that the field is valid.

* Create place to hold the results.
DATA: lp_data TYPE REF TO DATA.
FIELD-SYMBOLS: <value> TYPE ANY.

CREATE DATA lp_data TYPE (field).
ASSIGN lp_data->* to <value>.

* Do the dynamic selection
SELECT SINGLE (field) INTO <value> FROM my_table WHERE ...

If you've more than one field to select, it gets more complicated, and you'll need instead of field, an internal table of type string_table, each record of which contains a field name. You'd probably have to use RTTS to define the structure that gets the value. The table can also be dynamically specified and the WHERE clause, in pretty much the same way. Read the help on SELECT for examples and more information.

matt