topic Re: Selection question in Application Development and Automation Discussions

Selection question

Former Member — Wed, 10 Oct 2007 13:21:46 GMT

Hello everyone!

I need to select a db table to have this result:


-------------     -------------
| A | B | C |     | A | D | E |
|-----------|     |-----------|
| 1 | A | A |     | 1 | A | A |
| 2 | B | B |     | 2 | B | B |
| 3 | C | C |     -------------
-------------

RESULT
--------------------
| A | B | C | D | E |
|-------------------|
| 3 | C | C |   |   |
---------------------

OR 

-------------
| A | B | C |
|------------
| 3 | C | C |
-------------

How do I accomplish this?

I accomplish this by using loops to delete the duplicate entries. Is it possible to be selected in one SELECT statement?

Thanks a lot!

Re: Selection question

Former Member — Wed, 10 Oct 2007 13:29:42 GMT

In where, the condition the keys must be different.

Ex: where a_{A <> b}A

Re: Selection question

Former Member — Wed, 10 Oct 2007 13:30:25 GMT

Hi,

Yes you can get the result that you wanted using a Select statement. This can be acheived by using Join conditions on the two tables.

With an inner join, you only get the records of the cross-product table for which there is an entry in all tables involved in the view.

With an outer join, records are also selected for which there is no entry in some of the secondary tables.

The set of hits determined by an outer join can therefore be a true set of the subset of hits determined with an inner join.

So for your scenario you can use Outer Join..

Thanks,

Suresh

Re: Selection question

Former Member — Wed, 10 Oct 2007 13:32:47 GMT

select A B C from tab1 into itab.

select A D E from tab2 into itab1.

loop at itab.

read table itab1 with key A = itab-A.

if sy-subrc = 0.

delete itab index sy-tabix.

else.

move corresponding itab to itab2.

move corresponding itab1 to itab2.

append itab2.

clear itab2.

endif.

endloop.

Re: Selection question

Former Member — Wed, 10 Oct 2007 13:34:24 GMT

Hi,

try this:

select * from table1

inner join table2

on table1_{A = table2}A

where table1~A = condition.

select * from table1

where conditions.

select * from table2

where A = table1-A.

append int_table.

endselect.

Re: Selection question

Former Member — Wed, 10 Oct 2007 13:37:22 GMT

creat 3 structures

1st useing A B C

2nd A D E

3rd A B C D E

select data from 1st db tabke and store in 1st internal table

select data from 2nd database table and store it in 2nd internal table for all entries in 1st internal table

loop at 1st internal table and put read on 2nd intenral table

move values of thesee to final internal table i.e 3 rd internal table

that all

reward if usefull

Re: Selection question

Former Member — Wed, 10 Oct 2007 13:53:22 GMT

hi Ricardo,

look if this is what u want plx.

DATA:

BEGIN OF i_table1 OCCURS 0,

END OF i_table1,

BEGIN OF i_table2 OCCURS 0,

END OF i_table2,

BEGIN OF i_table3 OCCURS 0,

a LIKE i_table1-a,

b LIKE i_table1-b,

c LIKE i_table1-c,

d LIKE i_table2-d,

e LIKE i_table2-e,

END OF i_table3.

i_table1-a = '1'.

i_table1-b = 'A'.

i_table1-c = 'A'.

APPEND i_table1.

i_table1-a = '2'.

i_table1-b = 'B'.

i_table1-c = 'B'.

APPEND i_table1.

i_table1-a = '3'.

i_table1-b = 'C'.

i_table1-c = 'C'.

APPEND i_table1.

i_table2-a = '1'.

i_table2-d = 'A'.

i_table2-e = 'A'.

APPEND i_table2.

i_table2-a = '2'.

i_table2-d = 'B'.

i_table2-e = 'B'.

APPEND i_table2.

LOOP AT i_table1.

READ TABLE i_table2 WITH KEY a = i_table1-a.

IF sy-subrc = 4.

APPEND i_table1 TO i_table3.

ENDIF.

ENDLOOP.

LOOP AT i_table3.

WRITE: / i_table3.

ENDLOOP.

Regards

Allan Cristian

Re: Selection question

Former Member — Sat, 13 Oct 2007 05:11:43 GMT

> In where, the condition the keys must be different.

> Ex: where a_{A <> b}A

Can you please elaborate? ^_

Thanks!

Re: Selection question

Former Member — Sat, 13 Oct 2007 05:13:20 GMT

> Hi,

> Yes you can get the result that you wanted using a

> Select statement. This can be acheived by using Join

> conditions on the two tables.

> With an inner join, you only get the records of the

> cross-product table for which there is an entry in

> all tables involved in the view.

> With an outer join, records are also selected for

> which there is no entry in some of the secondary

> tables.

> The set of hits determined by an outer join can

> therefore be a true set of the subset of hits

> determined with an inner join.

> So for your scenario you can use Outer Join..

> Thanks,

> Suresh

Hi! Is it ok if you will provide the SELECT statement? ^_

Thanks!

Re: Selection question

Former Member — Sat, 13 Oct 2007 05:22:43 GMT

Hi,

Try this..You can use sub query..

SELECT A B C D E

INTO TABLE Itab

FROM table1

WHERE NOT EXISTS ( select *

FROM table2

WHERE A = table1~A ).

Thanks

Naren

Re: Selection question

Former Member — Tue, 16 Oct 2007 03:55:48 GMT

Select all data using outer join then delete all records where D or E is not blank.