topic Re: Select in Application Development and Automation Discussions

Select

Former Member — Tue, 05 Aug 2008 10:32:11 GMT

Hi People,

Can you please tell me how can i use FOR ALL ENTRIES and ORDER BY in the same select.

pl mind that I do not want to use the Sort option after the select.

Thanks in advance.

Re: Select

Former Member — Tue, 05 Aug 2008 10:46:53 GMT

Hi,

You can not use ORDER BY Clause With FOR ALL ENTRIES .

Better you Select the Records through ALL Entries and then Sort the internal table By the keys.

Try this way-

SELECT lgnum                         " Warehouse no.
         tanum                         " Tranfer order no.
         tapos                         " Transfer order item.
         matnr                         " Material no.
         maktx                         " Material description.
         charg                         " Batch no.
         bestq                         " Stock category.
    FROM ltap
    INTO TABLE t_ltap
     FOR ALL ENTRIES IN t_lagp
   WHERE lgnum = t_lagp-lgnum
     AND tanum = t_lagp-btanr
     AND tapos = t_lagp-btaps
     AND bestq IN s_stocat
     AND matnr IN s_matnr
     AND charg IN s_batno .

  IF sy-subrc EQ 0.
   SORT t_ltap BY lgnum tanum tapos.
  ELSE.

Regards,

Sujit

Re: Select

bpawanchand — Tue, 05 Aug 2008 10:50:30 GMT

Your select stament in which you are using FOR ALL ENTRIES addition in the same query you cannot use ORDER BY clause

TABLES : spfli,sflight.

DATA :

t_spfli LIKE TABLE OF spfli WITH HEADER LINE,

t_sflight LIKE TABLE OF sflight WITH HEADER LINE.

SELECT

carrid

connid

cityfrom

cityto

INTO TABLE t_spfli

FROM spfli

UP TO 30 ROWS.

*SORT t_spfli BY carrid.

SELECT

carrid

connid

fldate

INTO TABLE t_sflight

FROM sflight

FOR ALL ENTRIES IN t_spfli

WHERE carrid EQ t_spfli-carrid

ORDER BY fldate ASCENDING.

*SORT t_sflight BY fldate.

check the above code

In teh Documentation it clearly mentions you as below

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.

Regards

Pavan

Re: Select

Former Member — Tue, 05 Aug 2008 10:51:33 GMT

hi,

You can't use FOR ALL ENTRIES and ORDER BY simultaneouly as ORDER BY comes with Aggregate function.

Regards,

Anirban

Re: Select

Former Member — Tue, 05 Aug 2008 10:51:46 GMT

Hi Mayank .,

If NOT t_lagp[] is initial .

SELECT lgnum

tanum " Tranfer order no.

tapos " Transfer order item.

matnr " Material no.

maktx " Material description.

charg " Batch no.

bestq " Stock category.

FROM ltap

INTO TABLE t_ltap

FOR ALL ENTRIES IN t_lagp

WHERE lgnum = t_lagp-lgnum

AND tanum = t_lagp-btanr

AND tapos = t_lagp-btaps

AND bestq IN s_stocat

AND matnr IN s_matnr

AND charg IN s_batno

order by lgnum ascending.

endif .

But Performance point of view better Use same select query with out Order By .

Use Sort statement after the selcet Query .

thankks

Sreenivas

Re: Select

Former Member — Tue, 05 Aug 2008 10:54:34 GMT

Hi Mayank,

I don't think we can have FOR ALL ENTRIES and ORDER BY clauses in one SELECT statement.

Regards,

Chandra Sekhar

Re: Select

Former Member — Tue, 05 Aug 2008 10:55:16 GMT

Hello Mayank,

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 which has FOR ALL ENTRIES.

Srinivas Reddy wrote..

Hi Mayank .,

If NOT t_lagp[] is initial .
SELECT lgnum
tanum " Tranfer order no.
tapos " Transfer order item.
matnr " Material no.
maktx " Material description.
charg " Batch no.
bestq " Stock category.
FROM ltap
INTO TABLE t_ltap
FOR ALL ENTRIES IN t_lagp
WHERE lgnum = t_lagp-lgnum
AND tanum = t_lagp-btanr
AND tapos = t_lagp-btaps
AND bestq IN s_stocat
AND matnr IN s_matnr
AND charg IN s_batno
order by lgnum ascending.
endif .

But Performance point of view better Use same select query with out Order By .

Use Sort statement after the selcet Query .

thankks
Sreenivas

the select stmt gives and error:

the addition FOR ALL ENTRIES in itab excludes sorting with ORDER BY fields.

Regards

Indu

Re: Select

Former Member — Tue, 05 Aug 2008 10:56:03 GMT

If the addition For All Entries is used in the WHERE condition, ORDER BY can only be used with the addition Primary key.

Check this code

tables: sflight, sbook.

DATA BEGIN OF gt_list OCCURS 0 .
        INCLUDE STRUCTURE sflight .
DATA END OF gt_list .

DATA BEGIN OF gt_display OCCURS 0 .
        INCLUDE STRUCTURE sbook .
DATA END OF gt_display .



SELECT *
      INTO TABLE gt_list
      FROM sflight .


 SELECT *
      INTO TABLE gt_display
      from sbook
      for all entries in gt_list
      where carrid = gt_list-carrid
      order by primary key.



      loop at gt_display.

      write :/ gt_display-carrid.

     endloop.

Cheers

Snehi

Edited by: snehi chouhan on Aug 5, 2008 12:56 PM

Re: Select

Former Member — Tue, 05 Aug 2008 10:56:38 GMT

Hi,

u can do like this :

if Itab1 not initial.

Select Field1 field 2 from Table into Itab for all entries in Itab1 order by field 1.

endif.

here u r fetching field1 and field 2 from ur database table TABLE and storing in Itab, which u have created in ur report.

All this entries are fetched according to the entries un some othere internal table Itab1.

let me know if u still have some issues.

Reward the point if u find this useful.

Re: Select

Former Member — Tue, 05 Aug 2008 10:58:26 GMT

you have to specify PRIMARY KEY addition in ORDER BY else you cannot use ORDER BY in FOR ALL ENTIRES, it will give you a error, then in this case you have to sort the table after select.

With luck,

Pritam.

Re: Select

Former Member — Tue, 05 Aug 2008 11:00:50 GMT

Mayank,

am sorry to say that you got some misleads in some above replies.

ofcource we can use ORDER BY with FOR ALL ENTRIES.

but there is certaion condition for that:

statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.

Amit.

Re: Select

Former Member — Tue, 05 Aug 2008 11:02:06 GMT

Hi,

if you are using FOR ALL ENTRIES then order by can only be used the addition 'PRIMARY KEY'. so that you should exculde the order by in your select query.

Regards,

Boobalan S