IDMS: SQL union does not return rows in correct order

Products

• IDMS

Issue/Introduction

The rows in a UNIONed query are not being returned in order.

Example:

SELECT 'DEPT_HEAD_ID = ' AS FIELD, DEPT_HEAD_ID_0410 AS VALUE
FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000
UNION
SELECT '     DEPT_ID = ' AS FIELD, DEPT_ID_0410 AS VALUE
FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000;
*+
*+ FIELD             VALUE
*+ -----             -----
*+      DEPT_ID =     1000
*+ DEPT_HEAD_ID =       13
*+
*+ 2 rows processed

Environment

Release : All supported releases.
Component : IDMS SQL Option

Cause

The order of the SELECTs in a UNIONed query has no influence on the ultimate order of the rows in the output.

 

Resolution

In an SQL SELECT statement, the only way to guarantee the order in which rows are returned is with an ORDER BY clause.

One suggestion is to introduce a dummy sequencing column and put an ORDER BY clause on it.

SELECT 'DEPT_HEAD_ID = ' AS FIELD, DEPT_HEAD_ID_0410 AS VALUE, 1 AS SEQUENCE
FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000
UNION
SELECT '     DEPT_ID = ' AS FIELD, DEPT_ID_0410 AS VALUE, 2 AS SEQUENCE
FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000
ORDER BY SEQUENCE;
*+
*+ FIELD             VALUE     SEQUENCE
*+ -----             -----     --------
*+ DEPT_HEAD_ID =       13            1
*+      DEPT_ID =     1000            2
*+
*+ 2 rows processed

If you don't want the SEQUENCE column to appear in the output, make the main SELECT a sub-query.

SELECT FIELD, VALUE FROM
(SELECT 'DEPT_HEAD_ID = ' AS FIELD, DEPT_HEAD_ID_0410 AS VALUE, 1 AS SEQUENCE
FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000
UNION
SELECT '     DEPT_ID = ' AS FIELD, DEPT_ID_0410 AS VALUE, 2 AS SEQUENCE
FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000) X
ORDER BY X.SEQUENCE;
*+
*+ FIELD             VALUE
*+ -----             -----
*+ DEPT_HEAD_ID =       13
*+      DEPT_ID =     1000
*+
*+ 2 rows processed