Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Reg,arding select

Former Member

‎2007 Jul 10 11:51 AM

0 Likes
974

Hi experts

4 REPLIES 4
Read only

Former Member

‎2007 Jul 10 12:10 PM

0 Likes
920

<b>SELECT</b>

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

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

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

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

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

<b>... HAVING <cond></b>

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.

<b>ORDER BY</b> clause

Sorts the lines of the selection.

Syntax

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

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

The select command is the most fundamental function of writing ABAP programs allowing the retrieval ofdata from SAP database tables. Below are a few examples of the various ways of selecting data.

Example :


*Code to demonstrate select command
*Code to demonstrate select into internal table command
TYPES: BEGIN OF t_bkpf,
*  include structure bkpf.
  bukrs LIKE bkpf-bukrs,
  belnr LIKE bkpf-belnr,
  gjahr LIKE bkpf-gjahr,
  bldat LIKE bkpf-bldat,
  monat LIKE bkpf-monat,
  budat LIKE bkpf-budat,
  xblnr LIKE bkpf-xblnr,
  awtyp LIKE bkpf-awtyp,
  awkey LIKE bkpf-awkey,
 END OF t_bkpf.
DATA: it_bkpf TYPE STANDARD TABLE OF t_bkpf INITIAL SIZE 0,
      wa_bkpf TYPE t_bkpf.

TYPES: BEGIN OF t_bseg,
*include structure bseg.
  bukrs     LIKE bseg-bukrs,
  belnr     LIKE bseg-belnr,
  gjahr     LIKE bseg-gjahr,
  buzei     LIKE bseg-buzei,
  mwskz     LIKE bseg-mwskz,         "Tax code
  umsks     LIKE bseg-umsks,         "Special G/L transaction type
  prctr     LIKE bseg-prctr,         "Profit Centre
  hkont     LIKE bseg-hkont,         "G/L account
  xauto     LIKE bseg-xauto,
  koart     LIKE bseg-koart,
  dmbtr     LIKE bseg-dmbtr,
  mwart     LIKE bseg-mwart,
  hwbas     LIKE bseg-hwbas,
  aufnr     LIKE bseg-aufnr,
  projk     LIKE bseg-projk,
  shkzg     LIKE bseg-shkzg,
  kokrs     LIKE bseg-kokrs,
 END OF t_bseg.
DATA: it_bseg TYPE STANDARD TABLE OF t_bseg INITIAL SIZE 0,
      wa_bseg TYPE t_bseg.


*Select directly into an internal table
SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
       dmbtr mwart hwbas aufnr projk shkzg kokrs
  FROM bseg
  INTO TABLE it_bseg.


* Select directly into an internal table where fields are in a
* different order or not all fields are specified 
SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
       dmbtr mwart hwbas aufnr projk shkzg kokrs
  FROM bseg
  INTO CORRESPONDING FIELDS OF TABLE it_bseg.


*Select... endselect command
SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
       dmbtr mwart hwbas aufnr projk shkzg kokrs
  FROM bseg
  INTO wa_bseg.

  APPEND wa_bseg TO it_bseg.
ENDSELECT.


*Select FOR ALL ENTRIES command
SELECT bukrs belnr gjahr bldat monat budat xblnr awtyp awkey
  UP TO 100 ROWS
  FROM bkpf
  INTO TABLE it_bkpf.

IF sy-subrc EQ 0.
* The FOR ALL ENTRIES comand only retrieves data which matches
* entries within a particular internal table.
  SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
         dmbtr mwart hwbas aufnr projk shkzg kokrs
    FROM bseg
    INTO TABLE it_bseg
    FOR ALL ENTRIES IN it_bkpf
    WHERE bukrs EQ it_bkpf-bukrs AND
          belnr EQ it_bkpf-belnr AND
          gjahr EQ it_bkpf-gjahr.
ENDIF.

reward points if it is usefull .....

Girish

Read only

Former Member

‎2007 Jul 10 12:29 PM

0 Likes
920

<b>SELECT</b>

<b>Reads data from the database.</b>

Syntax

SELECT <result> 
  INTO <target>
  FROM <source> 
  [WHERE <condition>]
  [GROUP BY <fields>] 
  [HAVING <cond>]
  [ORDER BY <fields>].

<b>

The SELECT statement consists of a series of clauses, each of which fulfils a certain task:</b>

SELECT clause

<b>Defines the structure of the selection.</b>

Syntax


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

<b>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 .</b>

INTO clause

<b>Defines the target area into which the selection from the SELECT clause is written.</b>

Syntax

...  INTO [CORRESPONDING FIELDS OF] <wa>
   | INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE <itab>
                                             [PACKAGE SIZE <n>]
   | INTO (<f1>, <f 2>,...)

<b>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.</b>

FROM clause

<b>

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

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>

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.

<b>The select command is the most fundamental function of writing ABAP programs allowing the retrieval ofdata from SAP database tables. Below are a few examples of the various ways of selecting data.</b>

Example :

*Code to demonstrate select command
*Code to demonstrate select into internal table command
TYPES: BEGIN OF t_bkpf,
*  include structure bkpf.
  bukrs LIKE bkpf-bukrs,
  belnr LIKE bkpf-belnr,
  gjahr LIKE bkpf-gjahr,
  bldat LIKE bkpf-bldat,
  monat LIKE bkpf-monat,
  budat LIKE bkpf-budat,
  xblnr LIKE bkpf-xblnr,
  awtyp LIKE bkpf-awtyp,
  awkey LIKE bkpf-awkey,
 END OF t_bkpf.
DATA: it_bkpf TYPE STANDARD TABLE OF t_bkpf INITIAL SIZE 0,
      wa_bkpf TYPE t_bkpf.
 
TYPES: BEGIN OF t_bseg,
*include structure bseg.
  bukrs     LIKE bseg-bukrs,
  belnr     LIKE bseg-belnr,
  gjahr     LIKE bseg-gjahr,
  buzei     LIKE bseg-buzei,
  mwskz     LIKE bseg-mwskz,         "Tax code
  umsks     LIKE bseg-umsks,         "Special G/L transaction type
  prctr     LIKE bseg-prctr,         "Profit Centre
  hkont     LIKE bseg-hkont,         "G/L account
  xauto     LIKE bseg-xauto,
  koart     LIKE bseg-koart,
  dmbtr     LIKE bseg-dmbtr,
  mwart     LIKE bseg-mwart,
  hwbas     LIKE bseg-hwbas,
  aufnr     LIKE bseg-aufnr,
  projk     LIKE bseg-projk,
  shkzg     LIKE bseg-shkzg,
  kokrs     LIKE bseg-kokrs,
 END OF t_bseg.
DATA: it_bseg TYPE STANDARD TABLE OF t_bseg INITIAL SIZE 0,
      wa_bseg TYPE t_bseg.
 
 
*Select directly into an internal table
SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
       dmbtr mwart hwbas aufnr projk shkzg kokrs
  FROM bseg
  INTO TABLE it_bseg.
 
 
* Select directly into an internal table where fields are in a
* different order or not all fields are specified 
SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
       dmbtr mwart hwbas aufnr projk shkzg kokrs
  FROM bseg
  INTO CORRESPONDING FIELDS OF TABLE it_bseg.
 
 
*Select... endselect command
SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
       dmbtr mwart hwbas aufnr projk shkzg kokrs
  FROM bseg
  INTO wa_bseg.
 
  APPEND wa_bseg TO it_bseg.
ENDSELECT.
 
 
*Select FOR ALL ENTRIES command
SELECT bukrs belnr gjahr bldat monat budat xblnr awtyp awkey
  UP TO 100 ROWS
  FROM bkpf
  INTO TABLE it_bkpf.
 
IF sy-subrc EQ 0.
* The FOR ALL ENTRIES comand only retrieves data which matches
* entries within a particular internal table.
  SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
         dmbtr mwart hwbas aufnr projk shkzg kokrs
    FROM bseg
    INTO TABLE it_bseg
    FOR ALL ENTRIES IN it_bkpf
    WHERE bukrs EQ it_bkpf-bukrs AND
          belnr EQ it_bkpf-belnr AND
          gjahr EQ it_bkpf-gjahr.
ENDIF.

reward points if it is usefull .....

Girish

Read only

Former Member

‎2007 Jul 10 12:44 PM

0 Likes
920

Hi

You can write select in so many ways depending on the declaration of internal table

1.SELECT SINGLE * FROM DB TABLE WHERE ...

2.SELECT F1 F2 INTO TABLE ITAB FROM DBTABLE WHERE...

3.SELECT F1 F2 INTO CORRESPONDING FIELDS OF ITAB FROM DB TABLE WHERE F1= ...

4.SELECT F1 F2 FROM DB TABLE UPTO 1 ROWS WHERE...

5.SELECT F1 F2 INTO (ITAB-F1, ITAB-F2) FROM DB TABLE WHERE..

APPEND ITAB.CLEAR ITAB.

ENDSELECT.

see the doc

SELECT result

FROM source

INTO|APPENDING target

[[FOR ALL ENTRIES IN itab] WHERE sql_cond]

.

...

.

SELECT is an Open-SQL-statement for reading data from one or several database tables into data objects.

The select statement reads a result set (whose structure is determined in result) from the database tables specified in source, and assigns the data from the result set to the data objects specified in target. You can restrict the result set using the WHERE addition. The addition GROUP BY compresses several database rows into a single row of the result set. The addition HAVING restricts the compressed rows. The addition ORDER BY sorts the result set.

The data objects specified in target must match the result set result. This means that the result set is either assigned to the data objects in one step, or by row, or by packets of rows. In the second and third case, the SELECT statement opens a loop, which which must be closed using ENDSELECT. For every loop pass, the SELECT-statement assigns a row or a packet of rows to the data objects specified in target. If the last row was assigned or if the result set is empty, then SELECT branches to ENDSELECT . A database cursor is opened implicitly to process a SELECT-loop, and is closed again when the loop is ended. You can end the loop using the statements from section leave loops.

Up to the INTO resp. APPENDING addition, the entries in the SELECTstatement define which data should be read by the database in which form. This requirement is translated in the database interface for the database system´s programming interface and is then passed to the database system. The data are read in packets by the database and are transported to the current application server by the database server. On the application server, the data are transferred to the ABAP program´s data objects in accordance with the data specified in the INTO and APPENDING additions.

Reward points for useful Answers

Regards

Anji

Read only

Former Member

‎2007 Jul 12 1:45 PM

0 Likes
920

Hi Prakash,

There are different statements of select query

<b>Select statements</b>

select * from mara 
        where spart = 'PA'
          and not ( zatacd = space ) 
        order by matnr.

Gets information from Confirmed schedule file (EKES)

<b>Illustrates the usage of group by:</b> 

select ebeln eindt vbeln
            into (po_ebeln, po_eindt, po_vbeln)
            from ekes
            where ebeln = wa_vbfa-vbeln
            group by ebeln eindt vbeln.

<b>Joining the vendor files (LFA1 and LFB1)</b>

This outputs the fields into a table named TAB

select a1~sortl a1~name1 a1~name2         a1~pstlz  a1~ort01  a1~regio   a1~pfach           a1~land1   a1~lifnr   a1~stras          from lfa1 as a1 join lfb1 as b1                      on a1~lifnr = b1~lifnr                     into tab                   where a1~lifnr in s_lifnr                     and a1~loevm <> 'X'                     and b1~bukrs in s_bukrs                     and b1~loevm <> 'X'.

<b>Joining MARA and MBEW</b>

the material master and the accounting view by plant (gets plant cost for an item)

s_werks for mvke-dwerk obligatory. " Plant

select marc~matnr marc~werks marc~disgr mbew~salk3
 into table t_marc
 from marc join mbew on ( mbew~bwkey = marc~werks and
                         mbew~matnr = marc~matnr )
 where werks in s_werks.

Joining VBAP and VBUP

<b>This produces a list of open line items</b> 

loop at itab_vbak.
 select
  vbap~vbeln vbap~posnr vbap~matnr vbap~kwmeng vbap~werks vbap~arktx
  into bolines
  from vbap join vbup
  on ( vbap~vbeln = vbup~vbeln and vbap~posnr = vbup~posnr )
  where vbap~vbeln = itab_vbak-vbeln
  and lfsta = 'C'.
 endselect.

<b>Table ITAB_VBAK contains a list of open orders.</b>

<b>Reward pts if found usefull:)</b>

Regards

Sathish