topic Re: sql doubt in Application Development and Automation Discussions

sql doubt

Former Member — Tue, 04 Oct 2005 06:51:17 GMT

Why we using * infront fo the table.

SELECT SINGLE mtart

matnr

matkl

INTO (*mara-mtart,

*mara-matnr,

*mara-matkl)

FROM *mara

WHERE matnr = stpo-idnrk.

cheers.

bharadwaj

Re: sql doubt

Former Member — Tue, 04 Oct 2005 07:06:38 GMT

Hi,

If you use * it will select all the fields of the table.

Instead of * we will use Fields so that it will be more efficient in performance wise.

Re: sql doubt

Former Member — Tue, 04 Oct 2005 07:08:40 GMT

you don't have to use that. Declare a variable of type MARA and you can dump your values into that. Only SAP uses internally that notation.

Regards,

Ravi

Re: sql doubt

Former Member — Tue, 04 Oct 2005 07:14:06 GMT

Hi RaviKumar

Can u send that code ..if u have time plz .

i want detailed info.

Cheers.

Re: sql doubt

Former Member — Tue, 04 Oct 2005 07:19:29 GMT

Tables : MARA.

Data : wa_mara type MARA.

SELECT SINGLE mtart

matnr

matkl

INTO (wa_mara-mtart,

wa_mara-matnr,

wa_mara-matkl)

FROM mara

WHERE matnr = stpo-idnrk.

Regards,

Ravi

Note : Please reward points if this helps you.

Re: sql doubt

Former Member — Tue, 04 Oct 2005 07:22:51 GMT

Hi,

Please go thru this link.

Hope this will help you.

Sriram.

Re: sql doubt

Former Member — Tue, 04 Oct 2005 07:23:15 GMT

Thnx Ravi ........I sortedout the problem.

I awarded points to u .

Chrees

Bharadwaj

Re: sql doubt

Former Member — Tue, 04 Oct 2005 07:30:35 GMT

Hi bharadwaj,

below information may help you.

Defining Selections

The SELECT clause defines the structure of the result set (selection) that you want to read from the database.

The selection can be flat (one line) or tabular (several lines). You can specify whether to accept or exclude duplicate entries. The SELECT clause also specifies the names of the columns to be read. You can replace the names of the database fields with alternative names. Aggregate functions can be applied to individual columns.

The SELECT clause can be divided into two parts for lines and columns:

SELECT <lines> <cols> ...

<lines> specifies whether you want to read one or more lines. <cols> defines the column selection.

Reading a Single Line

To read a single entry from the database, use the following:

SELECT SINGLE <cols> ... WHERE ...

To ensure that the line can be uniquely identified, you must specify values for all of the fields of the primary key of the table in the WHERE clause. If the WHERE clause does not contain all of the key fields, the syntax check produces a warning, and the SELECT statement reads the first entry that it finds that matches the key fields that you have specified.

The result of the selection is either an elementary field or a flat structure, depending on the number of columns you specified in <cols>. The target area in the INTO clause must be appropriately convertible.

If the system finds a line with the corresponding key, SY-SUBRC is set to 0, otherwise to 4.

Reading Several Lines

To read a several entries from the database, use the following:

SELECT [DISTINCT] <cols> ... WHERE ...

If you do not use DISTINCT (<lines> is then empty), the system reads all of the lines that satisfy the WHERE condition. If you use DISTINCT, the system excludes duplicate entries.

The result of the selection is a table. The target area of the INTO clause can be an internal table with a line type appropriate for <cols>. If the target area is not an internal table, but a flat structure, you must include an ENDSELECT statement after the SELECT statement:

SELECT [DISTINCT] <cols> ... WHERE ...

...

ENDSELECT.

The lines are read in a loop one by one into the target area specified in the INTO clause You can work with the target area within the loop.

If at least one line is read, SY-SUBRC is set to 0 after the statement (or loop) has been processed. If no lines are read, SY-SUBRC is set to 4. The number of lines read is placed in the system field SY-DBCNT. Within the loop, SY-DBCNT already contains the number of lines that have already been passed to the target area.

Technically, it is possible to nest SELECT loops. However, for performance reasons, you should avoid doing so. If you want to read interdependent data from more than one database table, you can use a join in the FROM clause or a subquery in the WHERE clause.

Reading the Whole Line

To read all of the columns in the database table, use the following:

SELECT <lines> * ...

This reads all columns for the specified lines. The data type of the selected lines is a structure with exactly the same data type as the database table in the ABAP Dictionary. The target area of the INTO clause should be compatible with, or at least convertible into this data type. In the other clauses, you can only address the columns under their names in the database table.

Reading individual columns can be considerably more efficient than reading all of the columns in a table. You should therefore only read the columns that you need in your program.

Reading Single Columns

To read single columns from the database table, use the following:

SELECT <lines> <s1> [AS <a 1>] <s 2> [AS <a 2>] ...

where <s i > are single columns. There are different ways of addressing the columns, depending on the form of your FROM clause

example using * in select statement

Reading all columns of more than one line:

DATA WA TYPE SPFLI.

SELECT *INTO CORRESPONDING FIELDS OF WA

FROM SPFLI

WHERE CARRID EQ 'LH'.

WRITE: / SY-DBCNT,

WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.

ENDSELECT.

hope this helps you.

reward points for helpfull answers.

regards,

venu.

Re: sql doubt

sagar-acharya — Tue, 04 Oct 2005 10:33:00 GMT

Hi,

Just to add on to what Ravi has posted, you need not even declare the explicit work area WA_MARA. TABLES statement creates an implicit work area.

TABLES mara.

SELECT SINGLE mtart matnr matkl

FROM mara

INTO (mara-mtart, mara-matnr, mara-matkl)

WHERE matnr EQ variable.

However, in OO ABAP world it is not reccomended to use implicit work area.

/Sagar

Message was edited by: Sagar Acharya