SQL/R A.04.00 Release Notes

Welcome to the SQL/R A.04.00 release.

SQL/R provides SQL access to the Eloquence database.

The SQL/R ODBC Driver enables easy and transparent access to the Eloquence database from ODBC aware applications.
The SQL/R Report Generator provides an easy and flexible method to create reports on the fly, based on the SQL database query language.

Please note that the above two SQL/R product options are licensed separately.

The SQL/R A.04.00 release implements a number of enhancements.

Support for multiple SQL/R versions
SQL/R A.04.00 allows for multiple SQL/R instances to be active on a system and a parallel installation of multiple SQL/R versions. SQL/R A.04.00 is installed in a separate directory and multiple SQL/R instances may be configured. Please refer to the installation instructions for more information.
Enhanced SQL support
SQL/R 4.0 enhances SQL support including support for subselect and union select SQL constructs. The new release removes previous limitations and provides more efficency and improved performance.

Installation and Upgrade

Please refer to the SQL/R A.04.00 installation documents below for an overview of SQL/R installation and platform specific details, as well as information on updated License Keys.

License keys

SQL/R A.04.00 requires a license key version A.04.00 or above.

Please use the online license web form to obtain a license key at: license.marxmeier.com.

Alternatively, please contact the Marxmeier team at info@marxmeier.com to request a license key.

Please refer to the SQL/R license key documentation for more information.

Enhanced SQL support

More flexible use of * in select list
SELECT with optional FROM clause
SELECT DISTINCT
SQL Subqueries
UNION SELECT

More flexible use of * in the select list

The * in the select list may be combined with additional columns or use a table (or table alias name) to qualify any fields. In previous version, the * had to be the only entry in the select list.

select custno,name1,orders.* 
from customers,orders
where orders.custno=customers.custno;

SELECT with optional FROM clause

The FROM clause in a SELECT statement is now optional and may be omitted. If omitted, any values in the select list are used as results. This is useful for testing.

For example:

select 1234 as col;

     COL
    1234

SELECT DISTINCT to filter duplicate results

SELECT DISTINCT may be used to suppress identical result rows. Please keep in mind that it requires extra effort to scan results for duplicates and this also affects default result ordering.

For example:

select distinct matchcode
from customers
order by 1;

This query discards duplicate result rows and explicitly orders the results.

SQL Subqueries

A subquery is a SQL query nested inside a larger query. A subquery may occur in a

SELECT clause
FROM clause
WHERE clause

A subquery may be used in the FROM clause of a statement where the select statement may then transform or filter the results.

select custno, name
from (select custno, name1&" "&name2 as name from customers 
where matchcode like "KE%");

A subquery may be used in the select list and the output is then used as a column result.

select orderno, custno,
  (select name1 from customers 
   where customers.custno=orders.custno) as name
from orders;

This example obtains the customer name from the customer table for each order result. A subquery that references a result column (such as orders.custno above) is executed for each result.

A subquery is usually added within the WHERE clause of another SELECT statement.

You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.

A subquery must be enclosed in parentheses.
A subquery must be placed on the right side of the comparison operator.
Use single-row operators with single-row subqueries.

Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.

SQL Correlated Subqueries are used to select data from a table referenced in the outer query. The subquery is known as a correlated because the subquery is related to the outer query. In this type of queries, a table alias (also called a correlation name) must be used to specify which table reference is to be used.

select custno,matchcode,name1
from customers
where custno in
(select custno from orders
where custno in (select custno from orders)
group by custno
having count(*) > 3);

This outputs a list of customers with more then 3 orders. An IN subquery in a where condition must return a single column.

select custno,matchcode,name1
from customers
where exists
(select custno from orders
where custno=customers.custno);

The EXISTS subquery condition is true if the subquery returns at least one result. In the example, it verifies the customer has at least one order. The subquery must return a single column.

A subquery may also be used to compare against a relational operator. In this case the first result of the subquery is used.

select custno,name1 as "Name", TURNOVER__1 as "Sales Amt"
from customers
where TURNOVER__1 > (select max(TURNOVER__1)*0.90 from customers)
order by turnover__1
limit 5;

This example qualifies customers in the top 10% of sales for a month. As the subquery does not refer to another query it is executed only once. Other operators, such as = or <> may be used as well.

UNION SELECT

A UNION SELECT allows to combine multiple independent queries into a combined result (eg. combining independent tables).

Each select must have the same number of columns of the same type. Ordering affects the entire result.

For example:

select 111 as col union select 222 union select 333;

     COL
     111
     333
     222

By default, a union select removes any duplicate results.

select 111 as col union select 111;

     COL
     111