Subqueries

---

A subquery is a query that the main SELECT statement calls to determine the condition values. For example, in the EFASHION universe, certain clothing articles (SKU number) were promoted during various months in the year 2000. You would like to know sales for these articles by store in 2001. Did the promotions increase sales in the following year? Store does not directly apply to product promotions, so if you tried to retrieve all this information in one query, the results would be split into separate tables, as shown on the right in Figure 22-1. The solution is to use a subquery to retrieve a list of articles that were promoted in 2000 and see the effect on sales in 2001.

Creating a Subquery

When you specify a condition operand Create A Subquery ANY, BusinessObjects automatically inserts a new query tab named Subquery 1.1, as shown in Figure 22-7. When you create a subquery, there should be one, and only one, result object. The result object in the subquery must be the same object as the condition object in the main query. In Figure 22-7, this is SKU number. The conditions are what will vary between the main query and subquery. Notice in Figure 22-7 that the year conditions are different. The subquery looks for products that were promoted in 2000, but the main query displays sales for the products for a particular state in the subsequent year. Notice in the following SQL that there is another SELECT statement nested within the main query:

SELECT Calendar_year_lookup.Year, {fn concat('Q',Calendar_year_lookup.Quarter)}, Outlet_Lookup.Shop_name, Article_lookup.Article_code, Article_lookup.Article_label, sum(Shop_facts.Amount_sold) FROM Shop_facts,  Outlet_Lookup,  Calendar_year_lookup,  Article_lookup WHERE  ( Shop_facts.Shop_code=Outlet_Lookup.Shop_code  )   AND  ( Article_lookup.Article_code=Shop_facts.Article_code  )   AND  ( Shop_facts.Week_key=Calendar_year_lookup.Week_Key  )   AND  (  Outlet_Lookup.State  =  'New York'   AND  Calendar_year_lookup.Year  =  '2001'   AND  Article_lookup.Article_code   =  ANY    (SELECT     Article_lookup.Article_code     FROM  Calendar_year_lookup, Article_lookup,  product_promotion_facts,            promotion_lookup     WHERE       (  promotion_lookup.promotion_key=product_promotion_facts.promotion_key  )       AND  (  product_promotion_facts.Article_code=Article_lookup.Article_code  )       AND  (  product_promotion_facts.Week_key=Calendar_year_lookup.Week_Key  )       AND  (Calendar_year_lookup.Year  =  '2000'       AND  promotion_lookup.promotion  =  'y'  )) ) GROUP BY    Calendar_year_lookup.Year,    {fn concat('Q',Calendar_year_lookup.Quarter)},    Outlet_Lookup.Shop_name,    Article_lookup.Article_code,    Article_lookup.Article_label 

Figure 22-7: A subquery selects conditions based on the results of another query.

Calculation Subquery

The Calculation operand launches a Complex Condition Wizard that creates a particular kind of subquery using aggregate functions. For example, you want to know which particular wines are lower than the average wine price for each country. Recall that a standard subquery can contain only one result object. To generate a GROUP BY clause for country, you need two result objects, Average Price and Country. Therefore, you cannot answer this question with a standard subquery; the Calculation operand allows you to generate a GROUP BY clause while still comparing only one result column. By combining an aggregate function with a dimension, you can answer a number of complex business questions such as:

• Which products had lower sales this year versus last year (assuming year is a dimension and there are no time period objects)?

• When was the last order date for a particular customer?

• Which employees have higher than average travel expenses?

• Which customers take longer than the average number of days to pay?

For some of these questions, there may be better, more efficient ways to answer the question. If your universe contains time period objects such as This Year Sales and Last Year Sales, you can more easily calculate the variance between these two objects to determine which products had lower sales this year. If the universe designer includes an object Last Order Date, then you do not need a calculation subquery to retrieve this information. In fact, the calculation query is much less efficient than the universe designer creating a simple object MAX(ORDER_DATE). The example in this section focuses on finding wines priced lower than the average, a question not easily answered by other means.

Before launching the Complex Condition Wizard, it’s helpful to understand how some of the options work. Refer to Figure 22-8 for sample data retrieved from a main query on the left based on the possible values in the subquery on the right.

Figure 22-8: Sample wine data for a calculation subquery

Level of Calculation

For the level of calculation, you have two choices:

Global Retrieves one grand total and does not generate a GROUP BY clause. The query reads the entire database and presents one average price, regardless of country or any other dimensions. In Figure 22-8, a single value of $70, the average price for all wines, would form the condition.

By one or more objects When you select this option, you then specify the dimension objects for which you want to generate the GROUP BY. In this example, Country is the object for which you want to set the calculation level. In Figure 22-8, the subquery generates a list of average prices by country. These then will become the comparison values for the main query. If you wanted the query to be more precise—for example, to determine which wines are lower than the average price by Country as well as Varietal—you would specify these two dimension objects for the level of calculation.

Synchronizing the Calculation

If you choose a global level of calculation, the settings on this screen have no effect on the query, as you are always comparing only one value. I wish BusinessObjects would skip this screen when you select Global in the preceding step, as it only makes it more confusing! If you select a level of calculation by certain dimensions, however, then you must synchronize the calculation by the same dimension you specified for the level of calculation.

Independently of your objects When you choose this option, you are selecting any wines from the main query that have an average price less than or equal to any country average. The values are compared regardless of, or independent of, any dimension values.

For each value of one or more objects When you specify dimensions in the level of calculation, you should synchronize the calculation with the same dimension. For example, if you selected the Country dimension in the preceding section, you should select it here as well. Normally, the synchronization dimension would be exactly the same, because this option generates a join statement between the table in the main query and the subquery, as shown in the following SQL:

HAVING (       avg(WINE_FACT_PRICE_RATE.SalesPrice)  <=  ANY          ( SELECT            avg(SubAlias__2.SalesPrice)          FROM  WINE_FACT_PRICE_RATE  SubAlias__2          WHERE            SubAlias__2.Country = WINE_FACT_PRICE_RATE.Country          GROUP BY           SubAlias__2.Country )   )

In this respect, the subquery is executed for each Country in the main query until the subquery has retrieved a comparison value for each Country. When you specify “Independently,” the subquery is executed once. If you have two dimensions that are similar, such as Ship To Customer / Sold To Customer or Sending Plant / Receiving Plant, then it may be valid to specify one as the level of calculation and a different (but similar) object as the dimension upon which to synchronize.

Values to Compare

Finally, you select whether you want to compare one value (ALL) or any value (ANY). In this specific example, in which the comparison values are synchronized, using ALL or ANY has the same effect on the query results, as there is only one value to compare per country. In other examples, however, you may have multiple values from the subquery. For example, if the synchronization level is independently, then in Figure 22-8, there would be six average prices, one for each country. Selecting the option At Least One Value means that any prices that are less than ANY of the country averages will be retrieved. This is not too difficult, given the highest average price in the right-hand table is $163 for French wines. Selecting the option All Values means that only wines with a price less than the lowest one in the subquery, $18 in this case, would be retrieved.

Complex Condition Wizard

Having reviewed the different options in creating a calculation subquery, you are ready to use it in a condition. The purpose is to find wines with prices lower than the average for each country.

1. First create a query that contains the result objects, as shown in the left-hand table in Figure 22-8. Add the Average Price as a condition. Set the Operator to Less than or equal to.

2. From the list of Operands, double-click the Calculation operand. This launches the Complex Condition Wizard. You also may want to launch the wizard to later modify the condition selections.

3. When prompted, select an Object to use for comparison. In this example, Average Price, Click Begin >.

4. Define the level of calculation by one or more objects. Specify Country as the dimension object. Click Next >.

   

5. Synchronize the calculation by selecting For each value of one or more objects. Then select the same dimension specified in step 4. In this example, select the Country object. Click Next >.

   

6. At the next wizard prompt, select the number of values to compare to at least one value.

7. Click Finish and then Run to execute the query.

   Note 

   In the preceding example, Average Price was an existing object and allowed you to easily identify wines below the average. In many universes, the measure objects use the aggregate SUM, such as Store Sales, Margin, and Quantity Sold. If you wish to use a subquery to build queries based on the average, maximum, minimum, and so on, of particular measure objects, you must first ask the universe designer to create these objects either with the desired aggregate or as simple columns with no aggregate function. If the object exists in the universe without an aggregate function, the Complex Condition Wizard will prompt you to select an aggregate, thus giving you the most flexibility.

---