Extensions to ANSI SQL-92
Actuate SQL has the following extensions to ANSI SQL-92:
*
A parameterized query starts with a WITH clause that specifies the names and types of parameters that the query uses. The following example shows using parameters to specify returning rows where salesTotal is within a range specified by two parameters:
With ( minTotal Decimal, maxTotal Decimal )
Select o.id, o.date
from "/sales/orders.sma" o
where o.salesTotal between :minTotal and :maxTotal
A query with a parameterized SELECT statement is typed and is subject to the same casting rules as a function call, except that parameter declarations specify the maximum scale, precision, and length of parameter values. All parameter values are required. A parameter value must be a literal, for example 'abc', NULL, a parameter reference, or an Actuate SQL expression. A parameter value cannot be a column reference, for example ORDERS.ORDERID.
*
A parameterized table or view reference in a query enables specification of the query without knowing the table or view until run time. At run time, the values of the parameters specify the table. In the following example, the table is specified by the IOB name and the team and position parameters:
With( team VARCHAR, position VARCHAR, minGamesPlayed
INTEGER )
Select playername
From "/sports/baseball/japan/players.iob" [:team,:position]
Where GamesPlayed > :minGamesPlayed
Parameter passing is typed and is subject to the same casting rules as a function call.
*
A scalar subquery is a query that returns a scalar value that is used in a second query. For example, the following query returns a scalar value:
SELECT SUM(B.Quantity * B.UnitPrice)
FROM "Order Detail.sma" AS B
This second query uses the previous query as a scalar subquery, evaluating the result of the scalar subquery and checking if the result is greater than 1000:
SELECT CustomerID
  FROM "Customers.sma" C
  LEFT OUTER JOIN
  "Orders.sma" O
  ON ( C.CustomerID=O.CustomerID )
WHERE
  ( SELECT SUM(B.Quantity * B.UnitPrice)
  FROM "Order Detail.sma" AS B
  ) > 1000
*
In Actuate SQL, you can specify the algorithm to use for joins. There are three join algorithms in Actuate SQL:
*
A dependent join specifies obtaining all the results for the left side of the join and then using each resulting row to process the right side of the join. This algorithm is especially efficient when the left side of the join does not return many rows and the data source of the right side can handle evaluating the join criteria.
*
A nested loop join specifies obtaining and storing in memory all the results for the right side of the join. Then, for each row resulting from the left side, a nested loop join evaluates the right side results for matches to the join criteria. This algorithm is especially efficient when the right side of the join does not return many rows and the join expression cannot be delegated to the data source of the right side.
*
A merge join specifies obtaining the results for the right and left sides of the join and comparing these results row by row. Merge joins are applicable only for joins where the value on the left must be equal to the value on the right. This algorithm uses less memory than a nested loop join. This algorithm is especially efficient if the data sources sort the rows but presorting is not required.
The following example shows a merge join in a simple SELECT statement:
SELECT customers.custid, customers.customname, customers.city, salesreps.lastname, salesreps.email
FROM customers MERGE JOIN salesreps
ON customers.repid = salesreps.repid
The following example shows a dependent join in a parameterized SELECT statement:
With ( minRating INTEGER )
Select c.name, o.date, o.shippingStatus
FROM
  "/uk/customers.sma" c
DEPENDENT JOIN
  "/sales/orders.sma" o
On c.id = o.custId
Where c.rating >= :minRating
You can also specify whether the join is an inner join or left outer join. The following example shows a SELECT statement with a left outer join:
SELECT customers.custid, customers.contact_last, customers.contact_first, salesreps.lastname, salesreps.firstname
FROM salesreps LEFT OUTER JOIN customers
ON salesreps.firstname = customers.contact_first
For information about inner and outer joins, see the SQL reference guide for your database.
*
*
*
SELECT customers.contact_first || ' ' || customers.contact_last
  "MOST_VALUED_CUSTOMERS"
  FROM "/customers.sma" customers
  WHERE customers.purchasevolume > 3
  ORDER BY customers.purchasevolume DESC
If an ORDER BY item is not a SELECT item or an alias, it must be a grouping column if a GROUP BY clause is present. ORDER BY items must be SELECT items if SELECT DISTINCT is specified.
Use ORDER BY only when creating a query in a report designer. Do not use ORDER BY when you create an information object in Information Object Designer.
*
SELECT DATEPART('yyyy', orders.shipbydate) "YEAR",
  DATEPART('m', orders.shipbydate) "MONTH",
  COUNT(*) "NUM_ORDERS"
FROM "/orders.sma" orders
GROUP BY DATEPART('yyyy', orders.shipbydate),
  DATEPART('m', orders.shipbydate)
To use an expression as a GROUP BY item, the expression must appear as a SELECT item. Aggregate functions are not allowed in GROUP BY expressions unless they are outer references from a subquery and the subquery is contained in the HAVING clause of the parent query. Complex GROUP BY expressions cannot be used in the HAVING clause of the query.
*

Additional Links:

Copyright Actuate Corporation 2012