Close menu

SQL/R A.04.00 Release Notes (beta)

Welcome to the SQL/R A.04.00 beta test. SQL/R 4.0 allows for parallel installation and extends the supported SQL functionality and performance.

We appreciate your interest and would like to hear from your how this version works for you. Please send any feedback to support@marxmeier.com.

For general SQL/R documentation please refer to the Marxmeier web site: https://marxmeier.com/sqlr/docs.html.

Please note: This is a beta version. It is intended for test and evaluation use. It may not meet the release criteria for quality or performance.
Downloading and installing the software indicates your agreement to the beta test terms and conditions.

Supported platforms

Linux
SQL/R on Linux is supported for 64 bit systems and supports both 64 bit and 32 bit applications.
Experimental support for the 64-bit arm (aarch64) architecture was added. Support for the 32-bit x86 (i386) and IA64 (Itanium) architecture was discontinued.

HP-UX
SQL/R is available for HP 9000 and HP Integrity servers:
  • Itanium based systems: HP-UX 11iv2 (11.23) or later is required.
  • PA-RISC based systems: HP-UX 11iv1 (11.11) or later is required.

Windows
The SQL/R service is available for 64-bit (x64) Windows and supports both 64-bit and 32-bit applications.
On 32-bit Windows, only the 32-bit SQL/R ODBC drivers can be installed. On 64-bit Windows, both the 32-bit and the 64-bit SQL/R ODBC drivers are installed.

Required minimum Windows version:

  • Windows 7 (July 2016 update rollup, KB3172605)
  • Server: Windows Server 2008 Service Pack 2

Installation directory

Starting with SQL/R A.04.00, multiple SQLR versions may be installed and used in parallel. Each major version is installed into a separate directory while minor updates will update an existing version.

On HP-UX and Linux, SQL/R A.04.00 is installed in the /opt/sqlr/4.0 directory, configuration files are in /etc/opt/sqlr/4.0. When updating from a previous version, any configuration files need to be copied. A symlink may also be used as configuration files are fully compatible.

Please note: Previous versions created a symlink /opt/sqlr to point to /opt/sqlr2. This symlink is removed during the installation.

The Windows installation program has the option to migrate the previous configuration files and/or remove the previous SQL/R version.

Installation notes

SQL/R installation follows the conventions of the respective platform. For a detailed description, please refer to the SQLR 3.3x installation instructions.

The following platform specific notes describe any differences:

Linux
By default If available, SQL/R uses systemd to manage startup and instances. SQL/R uses the sqlr40.service and sqlr40@.service unit files. When systemd is not available, the configuration file /etc/default/sqlr40 is used for configuring the start/stop scripts and the automatic startup on system boot. Use /etc/init.d/sqlr40 to start or stop SQL/R. For details, please refer to the Linux installation instructions.

HP-UX
The configuration file /etc/rc.config.d/sqlr40 is used for configuring the start/stop scripts and the automatic startup on system boot. Use /sbin/init.d/sqlr40 to start or stop SQL/R. For details, please refer to the HP-UX installation instructions.

Windows
Please refer to the Windows installation instructions.

License keys

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

If an Eloquence Personal Edition is used, a two user license is granted automatically.

On HP-UX and Linux, the license file is in /etc/opt/sqlr/4.0/license. During the first installation on HP-UX a temporary license is created that is valid for four weeks.

If an Eloquence Personal Edition is used, a two user license is granted automatically.

To request a SQL/R license key, please contact sales@marxmeier.com.

Enhanced SQL support

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

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.

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