Dynamic SQL oder n-records with condition

[Former Member]

Hi All,

assming I got a database table just with the fields

• firstname
• lastname

assumung the table is filled with:

firstname	lastname
peter	nixon
paul	nixon
mary	nixon
john	kennedy
james	kennedy
anna	kennedy
julia	kennedy

On the selection screen there is only the option for the firstnames.

If the user fills the firstnames with peter, paul, mary  I want to get nixon

If the user fills the firstnames with peter mary  I want to get a sy-subrc NE 0, because I do not find a matching lastname 

If the user fills the firstnames with peter, john,  mary  I want to get a sy-subrc NE 0, because I do not find a matching

This is tricky because the select should be dynamic.

With oder word: It could have families with e.g. 10 members.

Thanks

Regards

Mario

[Former Member]

Hello,

Not sure to get your point correctly, but I guess you could play around with a outer join on the same table (using 2 different aliases and removing duplicates afterwards) and then check your result table against input entries to find out if you have a full match or not. In other words, with PETER, PAUL, MARY you will get 3 entries with the same lastname (means it's a match); With PETER, MARY you will get the same 3 entries (so 2 input for 3 given in the result set, so no matching), ...

Br,

Manu.

[Former Member]

Hi Manu,

thanks for your reply. I already considered your suggestions. I'd like to find a appropriate SQL Statement.

Regards

Mario

[Former Member]

Well something like:

select a~firstname a~lastname from ztable as a

    left outer join ztable as b on a~lastname = b~lastname

   into [corresponding fields of] table itab

where a~firstname in t_firstname_range.

sort itab.

delete adjacent duplicates from itab.

Br,

Manu.

[oliver_wurm]

Hi Mario,

I do not think you will be able to do what you have described in ONE SQL Statement. But in two it should be possible. I I had this Problem to solve I would run 2 SELECTs:

1. SELECT lastname Count(*) INTO TABLE ... FROM <your table> GOUP BY lastname.

2. SELECT lastname Count(*) INTO TABLE ... FROM <your table> GOUP BY lastname

    WHERE firstname in <your select-Option>.

All entries which have in both result tables the same number in column 2 with the same lastname in column 1 are a match.

Regards

Oliver