topic Re: Dynamic SQL oder n-records with condition in Application Development and Automation Discussions

Dynamic SQL oder n-records with condition

Former Member — Tue, 23 Feb 2016 10:51:59 GMT

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

Re: Dynamic SQL oder n-records with condition

Former Member — Tue, 23 Feb 2016 13:42:00 GMT

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.

Re: Dynamic SQL oder n-records with condition

oliver_wurm — Tue, 23 Feb 2016 17:57:01 GMT

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

Re: Dynamic SQL oder n-records with condition

Former Member — Wed, 24 Feb 2016 05:20:34 GMT

Hi Manu,

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

Regards

Mario

Re: Dynamic SQL oder n-records with condition

Former Member — Thu, 25 Feb 2016 08:53:17 GMT

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.