You apply a further restriction on an object by adding a condition in the Where box from the Definition page of the Edit Properties dialog box for an object. You can define the condition at object creation, or add it to the object definition at any time.
In a universe, the Where clause in an SQL statement can be used in two ways to restrict the number of rows that are returned by a query.
· A Where clause is automatically inferred in the Select statement for an object by joins linking tables in the schema. Joins are usually based on equality between tables. They prevent Cartesian products being created by restricting the data returned from joined tables.
· You add a condition in the Where clause for an object. This is an additional condition to the existing Where clause inferred by joins. You define a Where clause to further restrict the data that is returned in a query, for example when you want to limit users to queries on a sub-set of the data.
The report below is an unrestricted block containing data for sales people from all countries:
The SQL for this query appears below. The Where clause contains only restrictions inferred by the joins between the tables Customer, City, Region, and Sales_Person.
SELECT
Sales_Person.sales_person,
Country.country
FROM
Sales_Person,
Country,
Region,
City,
Customer
WHERE
( City.city_id=Customer.city_id )
AND ( City.region_id=Region.region_id )
AND ( Country.country_id=Region.country_id )
AND ( Sales_Person.sales_id=Customer.sales_id )
If you want to restrict users to see only returned values specific to France, you can add a condition to the Where clause of the Country object. The following report shows sales people for France only:
The SQL for the query is as follows:
SELECT
Sales_Person.sales_person,
Country.country
FROM
Sales_Person,
Country,
Region,
City,
Customer
WHERE
( City.city_id=Customer.city_id )
AND ( City.region_id=Region.region_id )
AND ( Country.country_id=Region.country_id )
AND ( Sales_Person.sales_id=Customer.sales_id )
AND ( Country.country = 'France' )
The Where clause has an additional line. This is the restriction that you have added to the Where clause of the Country object.
Apart
from self restricting joins, you should not create a join in a Where clause.
A join in a Where clause is not considered by Detect Contexts (automatic
context detection) or aggregate aware incompatibility detection. You should
ensure that all joins are visible in the Structure pane. This ensures
that all joins are available to the Designer automatic detection tools.