SELECT statement usage

The SELECT statement is used to retrieve data from one or more tables. The result is returned in a tabular result set.

When using the SELECT statement with the IMS Universal JDBC driver:

If you are selecting from multiple tables and the same column name exists in one or more of these tables, you must table-qualify the column or an ambiguity error will occur.
The FROM clause must list all the tables you are selecting data from. The tables listed in the FROM clause must be in the same hierarchic path in the IMS database.
In Java™ applications using the IMS JDBC drivers, connections are made to PSBs. Because there are multiple database PCBs in a PSB, queries must specify which PCB in a PSB to use. To specify which PCB to use, always qualify segments that are referenced in the FROM clause of an SQL statement by prefixing the segment name with the PCB name. You can omit the PCB name only if the PSB contains only one PCB.

Examples of valid IMS Universal JDBC driver SELECT queries

Selecting specified columns

The following statement retrieves the ward names and patient names from the WARD and PATIENT tables, respectively:

SELECT WARD.WARDNAME,PATIENT.PATNAME 
FROM PCB01.WARD, PATIENT

Selecting all columns with * symbol

The following statement retrieves all columns for the PATIENT table:

SELECT * 
FROM PCB01.PATIENT

The following statement retrieves the hospital name from the HOSPITAL table and all columns from the WARD table:

SELECT HOSPITAL.HOSPNAME, WARD.*
FROM PCB01.HOSPITAL, PCB01.WARD

Selecting with DISTINCT

The following statement retrieves all distinct patient names from the PATIENT table:

SELECT DISTINCT PATNAME
FROM PCB01.PATIENT

Selecting with ORDER BY

The ORDER BY clause is used to sort the rows. By default, results are sorted by ascending numerical or alphabetical order. The following statement retrieves all distinct hospital names, sorted in alphabetical order:

SELECT DISTINCT HOSPNAME FROM PCB01.HOSPITAL
   ORDER BY HOSPNAME

The following statement retrieves all ward names sorted in alphabetical order, and the number of patients in each ward sorted in ascending numerical order. If two WARDNAME values in the ORDER BY compare are equal, the tiebreaker will be their corresponding PATCOUNT values (in this case, the row with the numerically smaller corresponding PATCOUNT value is displayed first).

SELECT WARDNAME, PATCOUNT FROM PCB01.WARD
   ORDER BY WARDNAME, PATCOUNT

Use the DESC qualifier to sort the query result in descending numerical or reverse alphabetical order. The following statement retrieves all patient names in reverse alphabetical order:

SELECT PATNAME FROM PCB01.PATIENT
   ORDER BY PATNAME DESC

Use the ASC qualifier to explicitly sort the query result in ascending numerical or reverse alphabetical order. The following statement retrieves all ward names sorted in ascending alphabetical order, and the number of patients in each ward sorted in descending numerical order:

SELECT WARDNAME, PATCOUNT FROM PCB01.WARD
   ORDER BY WARDNAME ASC, PATCOUNT DESC

Selecting with GROUP BY

The GROUP BY clause is used to return results for aggregate functions, grouped by distinct column values. The following statement returns the aggregated sum of all doctors in every ward in a hospital, grouped by distinct ward names:

SELECT WARDNAME, SUM(DOCCOUNT)
FROM PCB01.WARD 
WHERE HOSPITAL_HOSPCODE = 'H5140070000H
   GROUP BY WARDNAME

The following statement returns the hospital name, ward name, and the count of all patients in each ward in each hospital, grouped by distinct hospital names and sub-grouped by ward names:

SELECT HOSPNAME, WARDNAME, COUNT(PATNAME)
FROM PCB01.HOSPITAL, WARD, PATIENT
   GROUP BY HOSPNAME, WARDNAME

Using the AS clause

Use the AS clause to rename the aggregate function column in the result set or any other field in the SELECT statement. The following statement returns the aggregate count of distinct patients in the PATIENT table with the alias of PATIENTCOUNT:

SELECT COUNT(DISTINCT PATNAME) 
   AS PATIENTCOUNT 
FROM PCB01.PATIENT

The following statement returns the aggregate count of distinct wards in all hospitals with the alias of WARDCOUNT, sorted by the hospital names in alphabetical order, and grouped by distinct hospital names (under a renamed column alias HOSPITALNAME):

SELECT HOSPNAME AS HOSPITALNAME, COUNT(DISTINCT WARDNAME) 
   AS WARDCOUNT
FROM PCB01.HOSPITAL, WARD
   GROUP BY HOSPNAME
   ORDER BY HOSPNAME