topic Re: Problems with select statement in Application Development and Automation Discussions

Problems with select statement

Former Member — Tue, 20 Jun 2006 09:04:39 GMT

Hi,

For some reason I cannot find the solution for the following problem.

I have an internal table.

Now I like to make an select over a database table, while only rows should be selected, where the key field occurs in the internal table and in case of identical key field only the row with the lowest value in another column is selcted.

Example:

- internal table:

col1 col2 col3

1 A AA

2 B AA

3 A AC

-database table:

col1 col2 col3

1 001 CD

1 002 CF

1 003 CG

2 001 CD

2 002 CF

2 003 CG

3 002 CF

3 003 CG

4 001 CD

4 002 CF

4 003 CG

- the selected database entries should be

1 001 CD

2 001 CD

3 002 CF

explication:

- entries with 4 in the first column do not occur in the internal table, so they do not appear in the selection

- in case of more than one entry in the database table only the one with the lowest value in column two will be selected

If you have any idea how this could be solved, I would really appreciate you help.

regards

Torsten

Re: Problems with select statement

Former Member — Tue, 20 Jun 2006 09:10:28 GMT

Hi,

sort table <internal table> by <internal table>-col1.

If NOT <internal table>[] is initial.

Select col1 col2 col3

from <database table>

into ......

FOR ALL ENTRIES IN TABLE <internal table>

where col1 = <internal table>-col1.

Endif.

Hope this will help you.

Re: Problems with select statement

Former Member — Tue, 20 Jun 2006 09:18:10 GMT

is it solved? Use FOR ALL ENTRIES option.automatically it will besolaved

Re: Problems with select statement

Former Member — Tue, 20 Jun 2006 09:22:08 GMT

use for all enteries in selct query and in where clause give the where condition as fieldname = itab-fieldname.

Reward points if it helps

regards

Gunjan

Re: Problems with select statement

Former Member — Tue, 20 Jun 2006 09:23:59 GMT

sort itab1 by col1.

select col1 col2 col3 into table itab2 for all entries in  itab1 where col1 = itab1-col1.

sort itab2 by col1 col2.

loop at itab2.
  at new col1.
  clear flag.
  l_tabix = sy-tabix.
  read table itab2 index l_tabix.
  flag = 'x'.
  endat.
 if flag ne 'x'.
   delete itab2 index l_tabix.
endloop.

Re: Problems with select statement

Former Member — Tue, 20 Jun 2006 09:25:25 GMT

Hi,

1) u have to select based on internal table 1

2) u have to filter the internal table 2 for minimum value.

select col1 col2 col3 
from <any table>
into table <Internal_table_final>
for all entries in <Internal_table_1>
where col1 = Internal_table_1-col1.

sort Internal_table_final by col1 col2.

loop at Internal_table_final.

   at new col1.
      continue.
   endat.    
   delete Internal_table_final.

endloop.

try this

Regards

Mark Helpful Answers

Message was edited by: Manoj Gupta

Re: Problems with select statement

Former Member — Tue, 20 Jun 2006 09:30:48 GMT

Hi Torsen,

use the foll logic...

Select * from db_tab into r_tab where col1 = sel_col1 .

Append r_tab.

endselect.

Sort r_tab ascending by col2.

Delete Adjacent duplicates from r_tab comparing col1.

Rgds,

Jothi.

Do Award pts for helful answers.