One of the new mapping operators introduced in 11gR2 is the much talked about (over the years) subquery filter operator. As well as the heterogeneous capabilities added, a number of enhancements to existing operators (such as joiner and lookup operator) and a few new ones were added. The subquery filter supports the SQL grammar for exists, in, no exists and not in.
This simple example illustrated here illustrates a scenario where we want to get all the addresses of customers where they have a high credit limit and are married for example. The filter on customers gets the customers who are married and have a credit limit > 10000;
The subquery filter operator is defined to use the Exists query type and a condition since we have a correlated subquery;
The generated SQL with the correlated subquery looks like this;
SELECT
"ADDRESSES"."CUSTOMER_ID" "CUSTOMER_ID",
"ADDRESSES"."PHONE_NUMBER" "PHONE_NUMBER",
"ADDRESSES"."CUST_STREET_ADDRESS" "CUST_STREET_ADDRESS",
"ADDRESSES"."CUST_POSTAL_CODE" "CUST_POSTAL_CODE",
"ADDRESSES"."CITY_ID" "CITY_ID"
FROM
"ADDRESSES" "ADDRESSES"
WHERE
(EXISTS (SELECT
1
FROM
"CUSTOMERS" "CUSTOMERS"
WHERE
( "CUSTOMERS"."MARITAL_STATUS" = 'married' ) AND
( "CUSTOMERS"."CREDIT_LIMIT" > 10000) AND
( "CUSTOMERS"."ID" = "ADDRESSES"."CUSTOMER_ID" ) ))
When the mapping is executed the result is a table with contact information;
The OWB mapping operators are all pluggable together, so we can take this example further to utilize a lookup operator for example, so the CITY_ID is also taken from addresses and we lookup the CITY_NAME and STATE_PROVINCE from the CITIES table, and the resultant table has this information.
So the mapping now looks like; 
The data after execution in the resultant table has;
So the subquery filter lets you model more scenarios than previous releases, there are still some cases that are not supported such as using less than (<) for example rather than exists/in etc. So the following style where clause 'where quantity < (select max(quantity) from sales where book_key = s.book_key)' cannot be modeled.
So what to do in this case? There is another new mapping related feature which is interesting is the in-line view operator. The view operator has an inline property such that if this is checked the SQL defined within OWB for the view will be placed 'in-line' in the generated code rather than the view name.
Here we have seen the subquery filter mapping operator introduced in 11gR2. The subquery filter supports the SQL grammar for exists, in, no exists and not in.
