Application Development 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: 

Select statement

Go to solution
Former Member

‎07-03-2007 10:48 AM

0 Kudos

Hi Guys,

Can you provide various select statements?

Points will be rewarded.

1 ACCEPTED SOLUTION

Go to solution
Former Member

‎07-03-2007 10:53 AM

0 Kudos

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 pcarrid pconnid ffldate bbookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON pcarrid = fcarrid AND

pconnid = fconnid )

INNER JOIN sbook AS b ON bcarrid = fcarrid AND

bconnid = fconnid AND

bfldate = ffldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

fseatsmax > fseatsocc.

11) SELECT scarrid scarrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid 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.

5 REPLIES 5

Go to solution
Former Member

‎07-03-2007 10:51 AM

0 Kudos

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

Go to solution
Former Member

‎07-03-2007 10:53 AM

0 Kudos

Hi,

<b>Code to demonstrate select command</b>

*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.

<b>*Select directly into an internal table</b>

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO TABLE it_bseg.

<b>* Select directly into an internal table where fields are in a

  • different order or not all fields are specified</b>

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.

<b>*Select... endselect command</b>

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.

<b>*Select FOR ALL ENTRIES command</b>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.

<b>* The FOR ALL ENTRIES comand only retrieves data which matches

  • entries within a particular internal table.</b> 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

Go to solution
Former Member

‎07-03-2007 10:53 AM

0 Kudos

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 pcarrid pconnid ffldate bbookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON pcarrid = fcarrid AND

pconnid = fconnid )

INNER JOIN sbook AS b ON bcarrid = fcarrid AND

bconnid = fconnid AND

bfldate = ffldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

fseatsmax > fseatsocc.

11) SELECT scarrid scarrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid 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.

Go to solution
mohammed_moqeeth
Active Participant

‎07-03-2007 10:57 AM

0 Kudos

Hi,

<b>below are some select statemets:</b>

<u>SELECT SINGLE.</u>

Select Single * into xmara from mara

where matnr = p_matnr.

<u>SELECT...... ENDSELECT</u>

Select * into xmara from mara.

EndSelect.

<u>SELECT * INTO TABLE</u>

Select * into table imara from mara

where mtart = 'HALB'.

<u>SELECT Clause</u>

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

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

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

<u>INTO clause</u>

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

<u>FROM Clause</u>

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

<u>WHERE Clause</u>

1. SELECT... WHERE cond

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

<u>GROUP-BY clause</u>

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

2. SELECT... GROUP BY (itab)

<u>ORDER BY clause</u>

1. SELECT.. ORDER BY PRIMARY KEY

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

3. SELECT... ORDER BY (itab)

<u>Go to se38.</u>

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.

Go to solution
former_member588853
Active Contributor

‎07-03-2007 11:00 AM

0 Kudos

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