topic Re: SELECT statement query in Application Development and Automation Discussions

SELECT statement query

Former Member — Thu, 12 Jun 2014 14:43:55 GMT

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?

Re: SELECT statement query

Former Member — Thu, 12 Jun 2014 14:50:32 GMT

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!

Re: SELECT statement query

raphael_almeida — Thu, 12 Jun 2014 15:07:14 GMT

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

Re: SELECT statement query

pratapbhng — Thu, 12 Jun 2014 15:55:58 GMT

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.

Re: SELECT statement query

VenkatRamesh_V — Thu, 12 Jun 2014 16:18:03 GMT

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.

Re: SELECT statement query

jrg_wulf — Thu, 12 Jun 2014 16:25:47 GMT

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

Re: SELECT statement query

raphael_almeida — Thu, 12 Jun 2014 19:51:03 GMT

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.

Re: SELECT statement query

Former Member — Sat, 14 Jun 2014 07:33:40 GMT

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.

Re: SELECT statement query

GirieshM — Sat, 14 Jun 2014 08:16:25 GMT

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

Re: SELECT statement query

raphael_almeida — Sat, 14 Jun 2014 15:41:34 GMT

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.

Re: SELECT statement query

Former Member — Sun, 15 Jun 2014 10:53:29 GMT

Thank you so much!