Simple SELECT

[Former Member]

Hi. I'm new to this forum so first I'd like to say hello!

I'd like to ask You how to write that SQL code in ABAP?

SELECT people.name
FROM people, trips
WHERE people.name = trips.name

[Former Member]

Hai,

SELECT name FROM people WHERE name = trips-name.

Regards,

Padmam.

[Former Member]

Hi Piotr,

Welcome to SDN.....

You can write Native SQL statements in ABAP using EXEC SQL...ENDEXEC statement.

To use a Native SQL statement, you must precede it with the EXEC SQL statement, and follow it with the ENDEXEC statement as follows:

EXEC SQL [PERFORMING <form>].

<Native SQL statement>

ENDEXEC.

There is no period after Native SQL statements. Furthermore, using inverted commas (") or an asterisk (*) at the beginning of a line in a native SQL statement does not introduce a comment as it would in normal ABAP syntax. You need to know whether table and field names are case-sensitive in your chosen database.

Otherwise, if you want to have equalent code in ABAP for your requirement use JOIN statement.

DATA:

BEGIN OF IT_NAME,

NAME TYPE PEOPLE-NAME,

END OF IT_NAME.

SELECT A~NAME

INTO TABNLE IT_NAME

FROM PEOPLE AS A INNER JOIN TRIPS AS B

ON A_{NAME = B}NAME.

Thanks,

Vinay

[Former Member]

Hi

Welcome To SDN!!!!!!!!..

You can Write like this..

SELECT name from people where name = trips-name.

Reward All Helpfull Answers....

[Former Member]

You dont need to specify the table name.

Simply write :

SELECT <colname1>

<colname2>

...............

from <TABNAME>

WHERE <if required>

[Former Member]

Hi,

Types : Begin of t_trips,

name type trips-name,

end of t_trips,

begin of t_people,

name type people-name,

end of t_people.

data : i_people type standard table of t_people,

i_trips type standard table of t_trips.

select name

from

trips

into table i_trips.

if not i_trips[] is initial.

select name

from

people

into table i_people

for all entries in i_trips

where name = i_trips-name.

endif.

i_people will have all the required data.

Best regards,

Prashant

[Former Member]

Hi Piotr,

You can use SAP help itself to learn more about SELECT.

Any way the syntax for SELECT is

SELECT result

FROM source

INTO|APPENDING target

[[FOR ALL ENTRIES IN itab] WHERE sql_cond]

[GROUP BY group] [HAVING group_cond]

[ORDER BY sort_key].

...

[ENDSELECT].

Regards

Sarath

reward points if found useful

[Former Member]

Hello,

Just go through thois document. This will be useful for u.

Syntax Diagram 
SELECT 


Basic form 
SELECT select clause [INTO clause] FROM from clause [WHERE cond1] [GROUP BY fields1] [HAVING cond2] [ORDER BY fields2]. 


In an ABAP Objects context, a more severe syntax check is performed that in other ABAP areas. See Short Forms Not Allowed and * Work Areas Not Allowed. 

Effect 
Reads a selection and/or a summary of data from one or more database tables and/or views (see relational database). SELECT is an OPEN SQL statement. 

Each SELECT statement consists of a series of clauses, each with a differen task: 


The SELECT clause select clause describes 

Whether the result of the selection should be a single record or a table, 

Which columns should be contained in the result, 

Whether identical lines may occur in the result. 



The INTO clause INTO clause determines the target area into which the selected data is read. If the target area is an internal table, the INTO clause specifies: 

Whether you want to overwrite the contents of the internal table or 

Append the results to the internal table, and 

Whether you want to place the data in the internal table in a single step, or in a series of packages. 



The INTO clause can also occur after the FROM clause. You may omit it if 

The SELECT clause contains a "*", 

The FROM clause does not contain a JOIN, and 

You have declared a table work area dbtab in your program using TABLES. 

The data, if it exists in the database, is then made available using the table work area dbtab. The statement is then processed further like the SELECT * INTO dbtab FROM dbtab statement, which has the same effect. 



If the result of the selection is a table, the data is normally read line by line (for further information, see INTO clause) in a processing loop, which is introduced with SELECT and concludes with ENDSELECT. The loop is processed once for each line that is read. If you want the result of the selection to be a single record, there is no concluding ENDSELECT statement. 



The FROM clause FROM clause specifies the source of the data (database tables or views), from which you want to select the data. It also specifies the 

Client handling, 

Behavior for buffered tables, and 

The maximum number of lines that you want to read. 



The WHERE clause cond1 specifies the conditions that the result of the selection must satisfy. By default, only data from the current client is selected (without you having to specify the client field specifically in the WHERE clause). If you want to select data from several clients, you must use the ... CLIENT SPECIFIED addition in the FROM clause. 



The GROUP BY clause fields1 combines groups of lines into single lines of the result table. A group is a set of records with the same value of each database field listed in the GROUP BY clause. 



The HAVING clause cond2 specifies conditions for the combined lines of the result table. 



The ORDER BY clause fields2 specifies how the records in the result table should be arranged. 



The system field SY-DBCNT contains the number of lines read so far ecah time the SELECT statement is executed. After ENDSELECT, SY-DBCNT contains the total number of records read. 

The return code is set as follows: 



SY-SUBRC = 0: 
The result table contains at least one record. 
SY-SUBRC = 4: 
The result table is empty. 
SY-SUBRC = 8: 
Applies only to SELECT SINGLE FOR UPDATE: You did not specify all of the primary key fields in the WHERE condition. The result table is empty. 


Note 
The SELECT COUNT( * ) FROM ... statement returns a result table containing a single line with the result 0 if there are no records in the database table that meet the selection criteria. In an exception to the above rule, SY-SUBRC is set to 4 in this case, and SY-DBCNT to zero. 



Example 
Displaying the passenger list for Lufthansa flight 0400 on 2/28/1995: 



DATA: WA_SBOOK TYPE SBOOK. 

SELECT * FROM SBOOK INTO WA_SBOOK 
  WHERE 
    CARRID   = 'LH '      AND 
    CONNID   = '0400'     AND 
    FLDATE   = '19950228' 
  ORDER BY PRIMARY KEY. 
  WRITE: / WA_SBOOK-BOOKID, WA_SBOOK-CUSTOMID, 

           WA_SBOOK-CUSTTYPE, WA_SBOOK-SMOKER, 
           WA_SBOOK-LUGGWEIGHT, WA_SBOOK-WUNIT, 
           WA_SBOOK-INVOICE. 
ENDSELECT. 



Note 
Performance: 



Storing database tables in a local buffer (see SAP buffering) can lead to considerable time savings in a client/server environment, since the access time across the network is considerably higher than that required to access a locally-buffered table. 



Notes 
A SELECT statement on a table for which SAP buffering has been declared in the ABAP Dictionary usually reads data from the SAP buffer without accessing the database. This does not apply when you use: 
- SELECT SINGLE FOR UPDATE or 
- SELECT DISTINCT in the SELECT clause, 
- BYPASSING BUFFER in the FROM clause, 
- ORDER BY f1 ... fn in the ORDER BY clause, 
- Aggregate functions in the SELECT clause, 
- When you use IS [NOT] NULL in the WHERE condition, 
or when the table has generic buffering and the appropriate section of the key is not specified in the WHERE condition. 



The SELECT statement does not perform its own authorization checks. You should write your own at program level. 



Proper synchronization of simultaneous access by several users to the same set of data cannot be assured by the database lock mechanism. In many cases, you will need to use the SAP locking mechanism. 



Changes to data in the database are not made permanent until a database commit (see LUW) occurs. Up to this point, you can undo any changes using a databse rollback (see Programming Transactions). At the lowest isolation level (see lock mechanism ), the "Uncommitted Read", it can sometimes be the case that data selected by a SELECT statement was never written to the database. While a program is selecting data, a second program could be adding data to, changing data in, or deleting data from the database at the same time. If the second program then executes a rollback, the first program has selected a set of data that may only represent a temporary state from the database. If this kind of "phantom data" is unacceptable in the context of your application, you must either use the SAP locking mechanism or change the isolation level of the database system to at least "Committed Read" (see locking mechanism). 



In a SELECT - ENDSELECT loop, the CONTINUE statement terminates the current loop pass and starts the next. 



If a SELECT - ENDSELECT loop contains a statement that triggers a database commit, the cursor belonging to the loop is lost and a program termination and runtime error occur. Remote Function Calls and changes of screen always lead to a database commit. The following statements are consequently not allowed wihtin a SELECT-ENDSELECT loop: CALL FUNCTION ... STARTING NEW TASK , CALL FUNCTION ... DESTINATION , CALL FUNCTION ... IN BACKGROUND TASK , CALL SCREEN, CALL DIALOG, CALL TRANSACTION, and MESSAGE. 



On some database systems (for example DB2/390) 
locking conflicts can be caused even by read access. You can prevent this problem from occurring using regular database commits. 



Related 
OPEN CURSOR, FETCH und CLOSE CURSOR 


Additional help 
Reading Data 

REgards,

Vasanth

[Former Member]

Hi Piotr Wojciechowski ,

Welcome to SDN!!!!

I am giving simple codes to write SQL stmts.

select <fld_list> from <table> into <itab> where <conditions>.

One easy way to learn is select SELECT keyword in ABAP and press f1 u will get help documentation or refer T Code ABAPDOCU for e.g. prgs.

Jogdand M B

[dev_parbutteea]

select name

from people

for all entries in trips

where name = trips-name.

[Former Member]

Hi ,

syntax for select statement...

SELECT result

FROM source

INTO|APPENDING target

[[FOR ALL ENTRIES IN itab] WHERE sql_cond]

[GROUP BY group] [HAVING group_cond]

[ORDER BY sort_key].

...

[ENDSELECT].

regards

sathish

[Former Member]

Hi,

Welocme to SDN.

• Strusture for name

Types : Begin of type_name,

name like people-name,

End of type_name.

• Internal table for name

Data: itab_name type standard table of type_name.

• Query

select p_{name into table itab_name from people as p join trips as t on p}name = t~name.

Thanks

Sandeep

Reward if helpful

[Former Member]

Thanks for lots of replies. I'm really glad. I tried to simplify my problem, but I think that It'll be better to give You my code

REPORT ZWOP_TEST1 .

TYPES : Begin of t_names,
          name type zpersonal-nachn,
        end of t_names.

DATA :  tmp TYPE t_names.


SELECT nachn INTO tmp FROM zpersonal
WHERE zintagral-card = zpersonal-stredisko_aktual.        ' here is the problem
  WRITE / tmp-name.
ENDSELECT.

Without WHERE line the code works and returns me names of people. But when I add WHERE line, I receive >Field "ZINTAGRAL_CARD" unknown<

[Former Member]

checkout the separator(_) , is it underscore or dash(-)

i guess it should be underscore(_)

zintagral<b>_</b>card

[Former Member]

Thanks for replying profusely and I'm sorry for chaos in that topic.

Could You explain to me the difference between ~ and -, because my workmate resolved our problem with code:

REPORT ZKNM_TEST1 .

TABLES: zpersonal, zintagral.

DATA: BEGIN OF pr,
  nachn LIKE zpersonal-nachn,
  fili LIKE zintagral-fili,
  rdate LIKE zintagral-rdate,
  rtime LIKE zintagral-rtime,
END OF pr.

SELECT zpersonal~nachn
       zintagral~fili
       zintagral~rdate
       zintagral~rtime
 FROM zpersonal
       JOIN zintagral
       ON zpersonal~stredisko_aktual = zintagral~card
    INTO CORRESPONDING FIELDS OF pr
    WHERE zintagral~RDATE = sy-datum
    GROUP BY zpersonal~nachn
             zintagral~fili
             zintagral~rdate
             zintagral~rtime.

    WRITE: pr-nachn, pr-fili, ' ' , pr-rdate, pr-rtime.
    ULINE.

ENDSELECT.

It's not so important at this moment what do the code do. Almost all of You use <b>table-field</b> notation. When i change ~ into - in code above it won't work.

Greetings

[Former Member]

~ applies only to join queries:

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor and ~ with the table name or a table alias.

Whenever we use alias or a table name(in your example from zpersonal~nachn, zpersonal is a table name), we need to use ~.

In simple queries:

there is no confusion between fields as there is only one table, so we do not use any kind of alias or table name and we directly refer to the field name.

SELECT NAME

INTO TABLE ITAB_NAME

FROM PEOPLE.

We also use - to refer to fields of internal table or structure.

Logically, both mean the same thing but they have different usage at different places.

[Former Member]

hi,,

SELECT people.name

FROM people, trips

WHERE people.name = trips.name

SELECT name from people into table itab for all entries in trips where a_{people = trips}people.

if helpful reward some points.

with regards,

suresh babu aluri.

[Former Member]

Hi,

<b>It is a simple JOIN query in ABAP too</b>.

*Declare an internal table to get the list of names.

data : int_people type standard table of people.

*Fetch data

<b>SELECT NAME

INTO TABLE INT_PEOPLE

FROM PEOPLE

INNER JOIN TRIPS

ON PEOPLE_{NAME = TRIPS}NAME</b>

[Former Member]

And how can I Write the content of <b>int_people</b> on the screen? Could You give me a tip?

[Former Member]

You will have to loop through the internal table int_people.

If you are familiar with Java then int_people is kind of a cursor, it is called internal table.

LOOP AT INT_PEOPLE.

write 😕 int_people-name.

ENDLOOP.

This will display all the names fetched by the query.

Hope this helps.

[Former Member]

REPORT ZWOP_TEST1 .

DATA :
  people TYPE STANDARD TABLE OF zpersonal.

SELECT nachn
INTO TABLE people
FROM zpersonal
INNER JOIN zintagral
ON zintagral~card = zpersonal~stredisko_aktual
WHERE zintagral~fili = '1200'
AND   zintagral~rdate = SY-DATUM.

LOOP AT people.
WRITE: /people-nachn.
ENDLOOP.

generates error: The internal table "PEOPLE" has no header line - explicit specification of an output area with "INTO wa" or "ASSIGNING <fs>" is required.

[Former Member]

DATA :

people TYPE STANDARD TABLE OF zpersonal,

wa_people like line of people.

SELECT nachn

INTO TABLE people

FROM zpersonal

INNER JOIN zintagral

ON zintagral_{card = zpersonal}stredisko_aktual

WHERE zintagral~fili = '1200'

AND zintagral~rdate = SY-DATUM.

LOOP AT people in wa_people.

WRITE: / wa_people-nachn.

ENDLOOP.

wa_people is an explicity work area which will hold one record at a time...to know more about work area, just search the forum once

if helpful, reward

Sathish. R

[Former Member]

I simply try to do

REPORT ZWOP_TEST1 .

DATA :
  people TYPE STANDARD TABLE OF zpersonal,
  wa_people LIKE LINE OF people.
  
SELECT nachn
INTO TABLE people
FROM zpersonal.

LOOP AT people INTO wa_people.
  WRITE: / wa_people-nachn.
ENDLOOP.

but nothing is printed to the screen

[dev_parbutteea]

REPORT ZWOP_TEST1 .

DATA :

people TYPE STANDARD TABLE OF zpersonal,

wa_people LIKE LINE OF people.

<b>SELECT nachn

INTO TABLE people

FROM zpersonal.</b>

LOOP AT people INTO wa_people.

WRITE: / wa_people-nachn.

ENDLOOP.

you haven't specified any conditions! there's no data in table people, so nothing is printed

[Former Member]

Hello,

Please check in debugg mode does the table people has any records selected from the table zpersonal.

Better do like this:

REPORT ZWOP_TEST1 .
 
DATA :
  people TYPE STANDARD TABLE OF zpersonal,
  wa_people LIKE LINE OF people.
  
SELECT nachn
INTO TABLE people
FROM zpersonal.
break-point.  " Also check the sy-subrc = 0.
" If Sy-subrc = 4. then the table zpersonal does not contain any records. 
LOOP AT people INTO wa_people.
  WRITE: / wa_people-nachn.
ENDLOOP.

Regards,

Vasanth