topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Fri, 13 Apr 2007 09:14:47 GMT

Hi,

I have doubt in select stmt. it is as follows.

select x y z from mara into table itab

for all entries in jtab where x = jtab-x.

Here x y z are non key fields and jtab-x is a key field.

Can we use non-key = key in for all entries.

Regards,

Ratna

Re: select statement

Former Member — Fri, 13 Apr 2007 09:16:31 GMT

HI ratna,

Yes we can do so....Make sure that,the data types of mara-x and jtab-x are the same.But you may get some duplicate records depending on the data in the tables.

Reward if helpful..

Regards,

V.Raghavender.

Re: select statement

Former Member — Fri, 13 Apr 2007 09:16:35 GMT

Hai,

Yes you can.

But there is chance of getting dupliate rows in the internal tbale.

Reward points if it helps you.

Regds,

Rama chary.Pammi

Re: select statement

Former Member — Fri, 13 Apr 2007 09:16:36 GMT

I do not see a problem in the select statement.. just make sure that the data types of mara-x and jtab-x are the same.

Hope this helps.

Sudha

Message was edited by:

Sudha Mohan

Re: select statement

Former Member — Fri, 13 Apr 2007 09:16:48 GMT

YES..you can use non-key fields.

but before using for all entries..sort the internal table by x and delete the duplicate entries. and also check if the table has any entries.

regards,

madhu

Re: select statement

Former Member — Fri, 13 Apr 2007 09:17:34 GMT

Hi,

Yes u can do that.

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

reward if helpful.

regards,

kiran kumar k.

Message was edited by:

kiran kumar

Re: select statement

Former Member — Fri, 13 Apr 2007 09:21:43 GMT

Hi Ratna,

U can very well use non-key = key in for all entries.

Make sure u sort ur int table and also delete adjacent duplicates after the select statements.

****reward points if useful.

Regards,

Pavithra

Re: select statement

Former Member — Fri, 13 Apr 2007 09:26:50 GMT

Hi...

yes, you can use but if u are using a non key field you may get duplicate records,

but for all entries eliminates the duplicate records in the result.

so it is advisable not use the non key fields..

you can see the following links,

it helps you a lot..

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/frameset.htm

/people/david.halitsky/blog/2006/08/21/anyone-got-some-real-benchmark-stats-on-for-all-entries

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

Hope it helps you...

Let me know if u have any more doubt...

Reward points if useful......

Suresh.......