topic A general Question? in Application Development and Automation Discussions

A general Question?

Former Member — Thu, 27 Sep 2007 11:47:12 GMT

Hello All,

I am acessing data from two tables with inner join statement.

If i access the same tables with open cursor seperately then

which statement is more affective?

If any know the solution? Please explain me clearly.

Regards,

Lisa

Re: A general Question?

Former Member — Thu, 27 Sep 2007 11:51:06 GMT

hi lisa,

good...

In the normal SELECT statement, the data from the selection is always read directly into the target area specified in the INTO clause during the SELECT statement. When you use a cursor to read data, you decouple the process from the SELECT statement. To do this, you must open a cursor for a SELECT statement. Afterwards, you can place the lines from the selection into a flat target area.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

SO Open cursor is comparitively good compared to inner join...

Pls reward if helpful...

Message was edited by:

Shori

Re: A general Question?

Former Member — Thu, 27 Sep 2007 11:51:19 GMT

Using a Cursor to Read Data

Opening and Closing Cursors

To open a cursor for a SELECT statement, use the following:

OPEN CURSOR [WITH HOLD] <c> FOR SELECT <result>

FROM <source>

[WHERE <condition>]

[GROUP BY <fields>]

[HAVING <cond>]

[ORDER BY <fields>].

You must first have declared the cursor <c> using the DATA statement and the special data type CURSOR. You can use all clauses of the SELECT statement apart from the INTO clause. Furthermore, you can only formulate the SELECT clause so that the selection consists of more than one line. This means that you may not use the SINGLE addition, and that the column selection may not contain only aggregate expressions.

An open cursor points to an internal handler, similarly to a reference variable pointing to an object. You can reassign cursors so that more than one points to the same handler. In a MOVE statement, the target cursor adopts all of the attributes of the source cursor, namely its position, and all of the clauses in the OPEN CURSOR statement.

You can also open more than one cursor in parallel for a single database table. If a cursor is already open, you cannot reopen it. To close a cursor explicitly, use the following statement:

CLOSE CURSOR <c>.

You should use this statement to close all cursors that you no longer require, since only a limited number of cursors may be open simultaneously. With one exception, a database LUW is concluded when you close a cursor either explicitly or implicitly. The WITH HOLD addition in the OPEN CURSOR statement allows you to prevent a cursor from being closed when a database commit occurs in Native SQL.

Reading Data

An open cursor is linked to a multiple-line selection in the database table. To read the data into a target area in the ABAP program, use the following:

FETCH NEXT CURSOR <c> INTO <target>.

This writes one line of the selection into the target area <target>, and the cursor moves one line further in the selection set. The fetch statement decouples the INTO clause from the other clauses in the SELECT statement. All the INTO clauses of the SELECT statement can be used. The statement reads the lines that are needed to fill the target area of the INTO clause and moves the cursor to the next line to be read.

SY-SUBRC is set to 0 until all the lines of the selection have been read; otherwise it is 4. After a FETCH statement, system field SY-DBCNT contains the number of all the lines read so far for the corresponding cursor.

REPORT demo_select_cursor_1.

DATA: c1 TYPE cursor,

c2 TYPE cursor.

DATA: wa1 TYPE spfli,

wa2 TYPE spfli.

DATA: flag1(1) TYPE c,

flag2(1) TYPE c.

OPEN CURSOR: c1 FOR SELECT carrid connid

FROM spfli

WHERE carrid = 'LH',

c2 FOR SELECT carrid connid cityfrom cityto

FROM spfli

WHERE carrid = 'AZ'.

DO.

IF flag1 NE 'X'.

FETCH NEXT CURSOR c1 INTO CORRESPONDING FIELDS OF wa1.

IF sy-subrc <> 0.

CLOSE CURSOR c1.

flag1 = 'X'.

ELSE.

WRITE: / wa1-carrid, wa1-connid.

ENDIF.

IF flag2 NE 'X'.

FETCH NEXT CURSOR c2 INTO CORRESPONDING FIELDS OF wa2.

IF sy-subrc <> 0.

CLOSE CURSOR c2.

flag2 = 'X'.

ELSE.

WRITE: / wa2-carrid, wa2-connid,

wa2-cityfrom, wa2-cityto.

ENDIF.

IF flag1 = 'X' AND flag2 = 'X'.

EXIT.

ENDIF.

ENDDO.

The output looks something like this:

The database table SPFLI is read using two cursors, each with different conditions.. The selected lines are read alternately in a DO loop.

REPORT demo_select_cursor_2.

DATA c TYPE cursor.

DATA wa TYPE sbook.

OPEN CURSOR c FOR SELECT carrid connid fldate bookid smoker

FROM sbook

ORDER BY carrid connid fldate smoker bookid.

FETCH NEXT CURSOR c INTO CORRESPONDING FIELDS OF wa.

WHILE sy-subrc = 0.

IF wa-smoker = ' '.

PERFORM nonsmoker USING c.

ELSEIF wa-smoker = 'X'.

PERFORM smoker USING c.

SKIP.

ELSE.

EXIT.

ENDIF.

ENDWHILE.

FORM nonsmoker USING n_cur TYPE cursor.

WHILE wa-smoker = ' ' AND sy-subrc = 0.

FORMAT COLOR = 5.

WRITE: / wa-carrid, wa-connid, wa-fldate, wa-bookid.

FETCH NEXT CURSOR n_cur INTO CORRESPONDING FIELDS OF wa.

ENDWHILE.

ENDFORM.

FORM smoker USING s_cur TYPE cursor.

WHILE wa-smoker = 'X' AND sy-subrc = 0.

FORMAT COLOR = 6.

WRITE: / wa-carrid, wa-connid, wa-fldate, wa-bookid.

FETCH NEXT CURSOR s_cur INTO CORRESPONDING FIELDS OF wa.

ENDWHILE.

ENDFORM.

The following is an extract from the list display:

The program opens a cursor for the database table SBOOK. After the first FETCH statement, a subroutine is called, which is dependent on the contents of the SMOKER column. The cursor is passed to an interface parameter in the subroutine. The subroutines read further lines until the contents of the SMOKER column change. The subroutines perform different tasks using the lines read by the cursor.

REPORT demo_select_cursor_3.

DATA: wa_spfli TYPE spfli,

wa_sflight TYPE sflight,

wa_sflight_back TYPE sflight.

DATA: c1 TYPE cursor,

c2 TYPE cursor.

OPEN CURSOR c1 FOR SELECT *

FROM spfli

ORDER BY PRIMARY KEY.

OPEN CURSOR c2 FOR SELECT *

FROM sflight

ORDER BY PRIMARY KEY.

DO.

FETCH NEXT CURSOR c1 INTO wa_spfli.

IF sy-subrc NE 0.

EXIT.

ENDIF.

WRITE: / wa_spfli-carrid, wa_spfli-connid.

DO.

IF NOT wa_sflight_back IS INITIAL.

wa_sflight = wa_sflight_back.

CLEAR wa_sflight_back.

ELSE.

FETCH NEXT CURSOR c2 INTO wa_sflight.

IF sy-subrc <> 0.

EXIT.

ELSEIF wa_sflight-carrid <> wa_spfli-carrid

OR wa_sflight-connid <> wa_spfli-connid.

wa_sflight_back = wa_sflight.

EXIT.

ENDIF.

WRITE: / wa_sflight-carrid, wa_sflight-connid,

wa_sflight-fldate.

ENDDO.

The output is as follows:

The program opens a cursor for each of the table SPFLI and SFLIGHT. Since both tables are linked by a foreign key relationship, it is possible to program a nested loop by sorting the selection by its primary key, so that the data read in the inner loop depends on the data in the outer loop. This programming method is quicker than using nested SELECT statements, since the cursor for the inner loop does not continually have to be reopened. If the group level in the inner loop is changed, the data that is read is stored temporarily until the next loop pass since it is not possible to reset the cursor.

Re: A general Question?

Former Member — Thu, 27 Sep 2007 11:51:34 GMT

innerjoin is better than open cursor

why because the cursor works on the buffer(data set) and it will take from the buffer itself..

but innerjoin will create just alias names and then joins by dynamically

it will fetch the data from two table with our own mentioned fields

so better to use inner join

Re: A general Question?

Former Member — Thu, 27 Sep 2007 11:52:38 GMT

Hello,

OPEN CURSOR [WITH HOLD] c FOR SELECT ... .

Effect

Opens a database cursor c in a database table or view for a SELECT statement. The variable c must be of the type CURSOR. You can use any SELECT statement that returns a table, but not a single record, as a result. When the cursor has been opened, the dataset specified with SELECT can be read with FETCH until the cursor is closed.

OPEN CURSOR belongs to the Open SQL command set.

If you attempt to open a cursor that has already been opened, you get a runtime error.

The following events close a cursor:

The CLOSE CURSOR statement.

The Open SQL statement COMMIT WORK

A database commit in Native SQL. In this case, a cursor opened with WITH HOLD is not closed.

The Open SQL statememnt ROLLBACK WORK

A database rollback in Native SQL

A screen change, in particular the statements CALL SCREEN, CALL DIALOG, CALL TRANSACTION, MESSAGE

A Remote Function Call, in particular the statements CALL FUNCTION ... DESTINATION , CALL FUNCTION ... STARTING NEW TASK, or CALL FUNCTION ... IN BACKGROUND TASK.

Example

Open the database cursor C1 in the database table SFLIGHT for the SELECT statement

SELECT * FROM SFLIGHT WHERE CARRID = 'LH '.

TABLES SFLIGHT.

DATA C1 TYPE CURSOR.

OPEN CURSOR C1 FOR

SELECT * FROM SFLIGHT WHERE CARRID = 'LH '.

Notes

In the above example, the OPEN statement contains no INTO clause. With cursor processing, you must always specify the target area for the selected data in the FETCH statement.

The OPEN CURSOR statement allows you to open several cursors at the same time in a table. Unlike with SELECT, you thus have several independent access paths to this table.

Since you can open only a restricted number of cursors at the same time, you should close cursors that are no longer required with CLOSE CURSOR.

Since the OPEN statement does not support authorization checks, you must program these yourself.

Regards,

LIJO

Re: A general Question?

Former Member — Thu, 27 Sep 2007 12:06:03 GMT

good

go through this which ll give the detail difference betwen the select and the open cursor statement

http://www.jt77.com/development1/programming-15805.html

reward point if helpful.

thanks

mrutyun^