topic Re: Select statement in Application Development and Automation Discussions

Select statement

Former Member — Tue, 03 Jul 2007 09:48:20 GMT

Hi Guys,

Can you provide various select statements?

Points will be rewarded.

Re: Select statement

Former Member — Tue, 03 Jul 2007 09:51:23 GMT

SELECT command - Information and example code of how to use the select command

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.

*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

Re: Select statement

Former Member — Tue, 03 Jul 2007 09:53:20 GMT

Hi,

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 commandSELECT 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 useful.

All the best

Re: Select statement

Former Member — Tue, 03 Jul 2007 09:53:31 GMT

Hi,

1) SELECT SINGLE carrid connid cityfrom cityto

INTO CORRESPONDING FIELDS OF wa

FROM spfli

WHERE carrid EQ 'LH' AND connid EQ '0400'.

2) SELECT carrid connid cityfrom cityto

INTO CORRESPONDING FIELDS OF TABLE itab

FROM spfli

WHERE carrid EQ 'LH'.

3) SELECT DISTINCT (list)

INTO CORRESPONDING FIELDS OF TABLE itab

FROM spfli.

4) SELECT carrid connid cityfrom cityto

INTO CORRESPONDING FIELDS OF TABLE itab

FROM spfli.

5) SELECT carrid connid

FROM spfli

INTO CORRESPONDING FIELDS OF TABLE itab

PACKAGE SIZE 3.

6) SELECT AVG( luggweight ) SUM( luggweight )

INTO (average, sum)

FROM sbook.

7) SELECT AVG( luggweight ) AS average SUM( luggweight ) AS sum

INTO CORRESPONDING FIELDS OF luggage

FROM sbook.

😎 SELECT *

INTO wa

FROM scarr UP TO 4 ROWS.

9) SELECT *

INTO wa

FROM (name) CLIENT SPECIFIED

WHERE mandt = '000'.

10) SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

11) SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid AND

p~cityfrom = 'FRANKFURT'.

12) SELECT carrid connid cityfrom cityto

INTO CORRESPONDING FIELDS OF line

FROM spfli

FOR ALL ENTRIES IN itab

WHERE cityfrom = itab-cityfrom AND cityto = itab-cityto.

13) SELECT carrid MIN( price ) MAX( price )

INTO (carrid, minimum, maximum)

FROM sflight

GROUP BY carrid.

14) SELECT connid

INTO wa-connid

FROM sflight

WHERE carrid = 'LH'

GROUP BY connid

HAVING SUM( seatsocc ) > 300.

15) SELECT carrid connid MAX( seatsocc ) AS min

INTO CORRESPONDING FIELDS OF wa

FROM sflight

GROUP BY carrid connid

ORDER BY carrid min DESCENDING.

Regards,

kavitha.

Re: Select statement

mohammed_moqeeth — Tue, 03 Jul 2007 09:57:48 GMT

Hi,

below are some select statemets:

SELECT SINGLE.

Select Single * into xmara from mara

where matnr = p_matnr.

SELECT...... ENDSELECT

Select * into xmara from mara.

EndSelect.

SELECT * INTO TABLE

Select * into table imara from mara

where mtart = 'HALB'.

SELECT Clause

1. SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

2. SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

3. SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)

INTO clause

1. SELECT... INTO wa

2. SELECT... INTO CORRESPONDING FIELDS OF wa

3. SELECT... INTO (f1, ..., fn)

4. SELECT... INTO TABLE itab

5. SELECT... INTO CORRESPONDING FIELDS OF TABLE itab

6. SELECT... APPENDING TABLE itab

7. SELECT... APPENDING CORRESPONDING FIELDS OF TABLE itab

FROM Clause

1. SELECT.... FROM dbtab [AS alias]

2. SELECT.... FROM (dbtabname)

3. SELECT.. FROM tabref1 [INNER] JOIN tabref2 ON cond

4. SELECT.. FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

WHERE Clause

1. SELECT... WHERE cond

2. SELECT... FOR ALL ENTRIES IN itab WHERE cond

GROUP-BY clause

1. SELECT.. GROUP BY f1 ... fn

2. SELECT... GROUP BY (itab)

ORDER BY clause

1. SELECT.. ORDER BY PRIMARY KEY

2. SELECT... ORDER BY f1 ... fn

3. SELECT... ORDER BY (itab)

Go to se38.

Click on the 'I' button.(CTRL+F8).

Give the string SELECT in the field ABAP Key Word and press enter. You will get all the documentation for SELECT statement.

Reward points if helpful.

regards,

moqeeth.

Re: Select statement

Former Member — Tue, 03 Jul 2007 10:00:06 GMT

HI,

1.select * from <DBTABLE> INTO <ITAB> WHERE <COND>.

2.select <FLD!> <FLD2> from <DBTABLE> INTO <ITAB> WHERE <COND>.

3.SELECT SINGLE <FLD1> <FLD2>

INTO CORRESPONDING FIELDS OF wa

FROM <DBTABLE> WHERE <COND>

4. select <fld>..

into <l_v_fld> ..

upto 1 rows

where <cond>.

endselect.

rewards if useful

regards,

nazeer