SELECT statement query

[Former Member]

Hello all,

         I understand this is a very basic question in ABAP. But, I was just having this doubt and would like to get it cleared before I move on with my development.

There is this piece of code:

  SELECT afield                                        
    FROM atable                                       
    INTO anothertable-afield                  
   WHERE afield EQ btable-afield            

     AND cfield EQ constantvalue.                           
  ENDSELECT.

Wanted to clarify whether the "ALL" the values from the btable will be compared with the afield values of atable. Or, do I need to loop this btable to ensure that all the values are compared one by one?

[Former Member]

If I have to use a FOR ALL ENTRIES, where could I use it in this case?

Is this usage right?

SELECT afield                                        
    FROM atable                                       
    INTO anothertable-afield  

    FOR ALL ENTRIES IN btable               
   WHERE afield EQ btable-afield            

     AND cfield EQ constantvalue.                           
  ENDSELECT.

Thanks!

[raphael_almeida]

Hi Alice,

Here a example code:

TYPE: BEGIN OF TY_ITAB,

               AFIELD TYPE ATABLE-FIELD " Here you assign an table field

           END OF TY_ITAB.

DATA: TI_ITAB   TYPE TY_ITAB OCCURS 0,

          WA_ITAB TYPE TY_ITAB.

SELECT A~AFIELD

   FROM ATABLE  AS A

   INNER JOIN BTABLE AS B

   ON A~AFIELD  EQ B~AFIELD

   INTO TABLE TI_ITAB

   WHERE A~CFIELD EQ CONSTANTVALUE.

READ TABLE TI_ITAB INTO WA_ITAB WITH KEY AFIELD = BTABLE-AFIELD.

Regards

[pratapbhng]

Hi Alice ,

        The code written will not serve your purpose. First of all I would suggest to not use ' Select ...endselect'. You can go for   'For all entries' or ' Inner Join'. Using SAP help you can come to know how to use this  For all entries and inner join.

Regards,

Pratap.

[jrg_wulf]

Hi Alice,

first things first

What do you do with the result of your select statement?

Depending on whether btable is an internal or a db-table, you can either use for all entries (internal) or join for external.

Assumed that btable is a db-table, you can use the join variant like Raphael already suggested, beeing the first choice if you where in need of additional fields from table btable as well.

Alternatively, you could use a subquery like this

SELECT afield FROM atable into workarea-afield

     WHERE  cfield eq constantvalue

     AND        afield = ( SELECT afield FROM btable ).

...

*     do something clever

     APPEND workarea to anothertable

...

ENDSELECT.

If you don't have to do anything clever, you can omit the select - Endselect loop and let the database do the heavy work.

SELECT afield FROM atable INTO CORRESPONDING FIELDS OF TABLE anothertable

     WHERE  cfield eq constantvalue

     AND        afield = ( SELECT afield FROM btable ).

This will populate your table anothertable in burstmode.

Hope it helps.

Best regards - Jörg

[raphael_almeida]

Adding what you said Jörg,

You can also (if you want to bring only one record) in this way:

DATA v_afield LIKE atable-field.

SELECT SINGLE afield

  FROM atable

  INTO v_afield

WHERE cfield EQ constantvalue.

Other way:

TYPE: BEGIN OF TY_ITAB,

        AFIELD TYPE ATABLE-FIELD " Here you assign an table field

      END OF TY_ITAB.

DATA WA_ITAB TYPE TY_ITAB.

SELECT afield

  FROM atable

  INTO wa_itab

WHERE cfield EQ constantvalue

UP TO 1 ROWS.

[VenkatRamesh_V]

Hi, Alice

from your statement it compared both atable and btable you dont need to check again.

But before using field in where condition.

check it is a key field or not ?

if not create Secondary Index.

Instead of Select and Endselect use Into table.

Regards,

Venkat.

[Former Member]

FOR ALL ENTRIES IN..........is the answer to your query, plus select...end select...causes  performance issue. So explore FOR ALL ENTRIES IN clause in OPEN SQL and use it accordingly.

[GirieshM]

Hi Alice,

Alice D' Souza wrote:


Hello all,
         I understand this is a very basic question in ABAP. But, I was just having this doubt and would like to get it cleared before I move on with my development.

There is this piece of code:


  SELECT afield                                         
    FROM atable                                        
    INTO anothertable-afield                   
   WHERE afield EQ btable-afield             
     AND cfield EQ constantvalue.                            
  ENDSELECT.


Wanted to clarify whether the "ALL" the values from the btable will be compared with the afield values of atable. Or, do I need to loop this btable to ensure that all the values are compared one by one?

To answer your question: Yes,your code will compare based on

1. the btable-afield should be a workarea i.e. it should not be table name

But there will be performance issue in your coding. So we suggest here to use for all entries syntax. Because any ways you wanna compare all values of the atable with btable.

Considering your requirement is to get the values from a table based on the values of the other.

If so,

* Getting the values of my first table...

Select * from table1

     into table it_table1. " This is your btable.. Add your where statement if anything exist.

* Now filling the second table based on 1st table.

Select * from table2

     into table it_table2

     for all entries in it_table1

     where field-of-table2 eq it_table1-field. " This is your atable

In this, it_table2 you ll get the expected comparison result

With Regards,

Giriesh M

[raphael_almeida]

Hi Alice!

Have you found the solution to your problem? If yes, please close this post as "Assumed Answered" (if you solve the problem alone) or "Answered" by selecting the correct answer among all posted here.

Please read this blog: it shows good practices as foreclosure postings.

Thanks.

[Former Member]

Thank you so much!