I'm working on an ABAP program and I have a filter table ZFILTER (CID, FID, ZFIELD, ZVALUE). The content ZFIELD and ZVALUE pairs like "country"+"DE", "date"+"Q1.2014". To illustrate this with an example:
1, 1, "country", "DE"
1, 2, "country", "SE"
1, 3, "date", "Q1.2014"
I need to query that table and find the CIDs of the pairs that correspond to my filter criteria. My filter criteria from front-end:
country=DE, date=Q1.2014
I now want to find all CIDs where the country is "DE" and date is "Q1.2014" and the expected return value of CID is 1.
I've tried the following query:
SELECT DISTINCT CID
FROM ZFILTER INTO TABLE LT_COMFILT
WHERE ( ZFIELD = 'country' AND ZVALUE = 'DE' )
AND
( ZFIELD = 'date' AND ZVALUE = 'Q1.2014' )
However, obviously, this gives me no result because of the AND between the statements. I'm guessing I'll have to create some sort of subquery but I'm struggling with this in ABAP as it exceeds my Open SQL expertise.
Is there a better way to go about this task or does anyone have input how to accomplish it (perhaps with a syntactical correct example of subquerying in Open SQL)?
If I understand your problem properly, it is precisely the motivation for the existence of the for all entries idiom in ABAP Open SQL:
data: ls_filter type zfilter,
lt_filter type table of zfilter,
lt_result type table of zfilter.
* Fill filter table
ls_filter-zfield = `country`.
ls_filter-zvalue = `DE`.
append ls_filter to lt_filter.
ls_filter-zfield = `date`.
ls_filter-zvalue = `Q1.2014`.
append ls_filter to lt_filter.
* Get result: Important: Don't execute if lt_filter is initial!
if lt_filter is not initial.
select * from zfilter into table lt_result
for all entries in lt_filter
where zfield = lt_filter-zfield
and zvalue = lt_filter-zvalue.
endif.