Home > Tools > Information Designer > Tips and Examples > Examples of Generated SQL > SQL – Subqueries

SQL – Subqueries

Information Designer supports subqueries (inner SQL). In this example we will demonstrate how to retrieve all sales transactions greater than the average.

We create a new column "High Sales Transactions" with the following filter expression:

%1 > (Select avg(Sales) from Sales.dbo.SalesandCost)

Note: Be careful to include all brackets or it will not work!

The column element defined above will result in the following SQL when executed as part of an information link (notice that the WHERE clause includes the sub query from the filter condition):

SELECT

S1."Sales" AS "HIGHSALESTRANSACTIONS"

FROM

"Sales"."dbo"."SalesandCost" S1

WHERE

(S1."Sales" >(

Select

avg(Sales)

from

Sales.dbo.SalesandCost

))

AND <conditions>