SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-199
MXCI Examples of SELECT
•
Specify the access mode after the ORDER BY clause:
SELECT common.isma_no
from sdcommon common
where common.sec_status='L'
UNION
SELECT main.isma_no
from sdmain main
where main.iss_eligible='Y'
ORDER BY 1 ascending for browse access;
For more about the effect of UNION on SELECT statements, including its effect on
performance, see the SQL/MX Query Guide.
MXCI Examples of SELECT
•
Retrieve information from the EMPLOYEE table for employees with a job code
greater than 500 and who are in departments with numbers less than or equal to
3000, displaying the results in ascending order by job code:
SELECT jobcode, deptnum, first_name, last_name, salary
FROM persnl.employee
WHERE jobcode > 500 AND deptnum <= 3000
ORDER BY jobcode
READ UNCOMMITTED ACCESS;
JOBCODE DEPTNUM FIRST_NAME LAST_NAME SALARY
------- ------- --------------- ----------- ----------
600 1500 JONATHAN MITCHELL 32000.00
600 1500 JIMMY SCHNEIDER 26000.00
900 2500 MIRIAM KING 18000.00
900 1000 SUE CRAMER 19000.00
. . .
In this example, because of READ UNCOMMITTED access, the query does not
wait for other concurrent processes to commit rows.
•
Display selected rows grouped by job code in ascending order:
SELECT jobcode, AVG(salary)
FROM persnl.employee
WHERE jobcode > 500 AND deptnum <= 3000
GROUP BY jobcode
ORDER BY jobcode;
JOBCODE EXPR
------- ----------------------
600 29000.00
900 25100.00
--- 2 row(s) selected.
This select list contains only grouping columns and aggregate functions. Each row
of the output summarizes the selected data within one group.