SELECT

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

Invocation

This statement can be used in COBOL or Java application programs, but the syntax is different.

For COBOL application programs, this is an executable statement that cannot be dynamically prepared.

Syntax for COBOL
Syntax for Java

Syntax for COBOL


                                          .-,---------.   
                                          V           |   
>>-SELECT--+-*------------------+--FROM ----| table |-+--------->
           | .-,--------------. |                         
           | V                | |                         
           '-----| column |---+-'                         

>--+-------------------------+---------------------------------->
   '-WHERE--search-condition-'   

>--+--------------------------------------+--------------------><
   |          .-,-----------------------. |   
   |          V                .-ASC--. | |   
   '-ORDER BY-----| column |---+------+-+-'   
                               '-DESC-'       

table

|--+----------------+---table-name------------------------------|
   '-schema-name--.-'                  

column

                                      (1)   
|--+-------------------+--column-name---------------------------|
   '---table-name--.---'

Notes:

You can have the same column name in multiple tables, but if the table is not qualified, an ambiguity check is performed to determine the table that the column belongs to.

Syntax for Java


>>-SELECT------------------------------------------------------->

>--+-*---------------------------------------------------------------+-->
   |              .-,----------------------------------------------. |   
   | .-ALL------. V                                                | |   
   '-+----------+-----| select-expression |--+-----------------+---+-'   
     '-DISTINCT-'                            '-AS column-alias-'         

          .-,----------------------------.   
          V                              |   
>--FROM ----| table |-+----------------+-+---------------------->
                      '-AS table-alias-'     

>--+----------------------------------------------------------------------------------------+-->
   | .-INNER-.      (1)                                                                     |   
   '-+-------+-JOIN-----| table |-+----------------+-ON-+-| column |---+-=-+-| column |---+-'   
                                  '-AS table-alias-'    '-column-alias-'   '-column-alias-'     

>--+-------------------------+---------------------------------->
   '-WHERE--search-condition-'   

>--+----------------------------------------+------------------->
   |          .-,-------------------------. |   
   |          V                  .-ASC--. | |   
   '-ORDER BY---+-| column |---+-+------+-+-'   
                +-column-alias-+ '-DESC-'       
                '-column-index-'                

>--+-------------------------------+---------------------------->
   |          .-,----------------. |   
   |          V                  | |   
   '-GROUP BY---+-| column |---+-+-'   
                +-column-alias-+       
                '-column-index-'       

>--+-------------------------------------------+---------------><
   |             .-1-------.                   |   
   '-FETCH FIRST-+---------+-+-ROW--+---ONLY---'   
                 '-integer-' '-ROWS-'              

select-expression

     .-math-operator-------------------.     
     V                                 |     
|--+---+----+-+-| column |-----------+-+-+----------------------|
   |   +-+--+ +-| literal |----------+   |   
   |   '- --' +-| aggregate |--------+   |   
   |          '-| numeric-function |-'   |   
   '-| date-functions |------------------'   

table

|--+----------------+---table-name------------------------------|
   '-schema-name--.-'                  

column

                                                          (2)   
|--+---------------------------------------+--column-name-------|
   '-+----------------+-+-table-name--.--+-'                    
     '-schema-name--.-' '-table-alias--.-'                      

math-operator

|--+-+--+-------------------------------------------------------|
   +- --+   
   +-/--+   
   '-*--'   

literal

|--+-'string-literal'-+-----------------------------------------|
   +-integer-literal--+   
   '-decimal-literal--'   

aggregate

               .-ALL------.                                 
|--+-+-SUM-+-(-+----------+-| aggregate-expression |-)--+-------|
   | +-AVG-+   '-DISTINCT-'                             |   
   | +-MIN-+                                            |   
   | '-MAX-'                                            |   
   '-COUNT(-+-*-------------------------------------+-)-'   
            | .-ALL------.                          |       
            '-+----------+-| aggregate-expression |-'       
              '-DISTINCT-'                                  

numeric-function

|--+-+-ABS-----+-(-| select-expression |-)-----------------------+--|
   | +-ACOS----+                                                 |   
   | +-ASIN----+                                                 |   
   | +-ATAN----+                                                 |   
   | +-CEIL----+                                                 |   
   | +-CEILING-+                                                 |   
   | +-COS-----+                                                 |   
   | +-COT-----+                                                 |   
   | +-DEGREES-+                                                 |   
   | +-EXP-----+                                                 |   
   | +-FLOOR---+                                                 |   
   | +-LN------+                                                 |   
   | +-LOG-----+                                                 |   
   | +-LOG10---+                                                 |   
   | +-SIGN----+                                                 |   
   | +-SIN-----+                                                 |   
   | +-SINH----+                                                 |   
   | +-SQRT----+                                                 |   
   | +-TAN-----+                                                 |   
   | +-TANH----+                                                 |   
   | '-RADIANS-'                                                 |   
   '-+-ATAN2-+-(-| select-expression |-,-| select-expression |-)-'   
     +-MOD---+                                                       
     '-POWER-'                                                       

date-functions

|--+-CURRENT_DATE------+--+-----------------+-------------------|
   +-CURRENT_TIME------+  '-(--precision--)-'   
   +-CURRENT_TIMESTAMP-+                        
   +-LOCALTIME---------+                        
   '-LOCALTIMESTAMP----'                        

aggregate-expression

   .-math-operator-------------------.   
   V                                 |   
|----+----+-+-| column |-----------+-+--------------------------|
     +-+--+ +-| literal |----------+     
     '- --' '-| numeric-function |-'

Notes:

JOIN tables must have referential integrity, expressed by the key field of a parent segment and the virtual foreign key of the dependent segment. You cannot specify both a comma separated list of tables and a JOIN statement.
You can have the same column name in multiple tables, but if the table is not qualified, each table must be searched for the column.

Description

The following keyword parameters are defined for the SELECT statement:

ALL

Retains all rows of the final result table and does not eliminate redundant duplicates. This is the default.

DISTINCT

Eliminates all but one of each set of duplicate rows of the final result table. Start of change

This keyword is valid only for Java application programs. End of change

Two rows are duplicates of one another only if each value in the first row is equal to the corresponding value in the second row. For determining duplicate rows, two null values are considered equal.

AS column-alias

Names or renames the result column. The name must be unique. Start of change

The AS clause is not supported in COBOL or .NET application programs. End of change

FROM table-name

Identifies the table from which rows are to be retrieved. The name must identify a segment in IMS™.

AS table-alias

Renames the table. The name must be unique. Start of change

The AS clause is not supported in COBOL or .NET application programs. End of change

INNER JOIN

JOIN

If a join operator is not specified, INNER is the default. The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

WHERE

Specifies the rows to be retrieved. You can omit the clause or give a search condition. When the clause is omitted, all the rows of the table are retrieved.

search-condition: Is any search condition as described in Search conditions. Each column-name in the search condition must identify a column of the table.
The search condition is applied to each row of the table and the retrieved rows are those for which the result of the search condition is true.

ORDER BY

The ORDER BY clause specifies an ordering of the rows of the result table.

A column, column-alias, or a column-index that specifies the value that is to be used to order the rows of the result of the table.

The column-index n identifies the nth column of the result table.

ASC: Uses the values of the column, column-alias, or column-index in ascending order. ASC is the default.
DESC: Uses the values of the column, column-alias, or column-index in descending order.

GROUP BY

The GROUP BY clause specifies a result table that consists of a grouping of the rows of intermediate result table that is the result of the previous clause.

The result of GROUP BY is a set of groups of rows. In each group of more than one row, all values of each column, column-alias, or column-index are equal, and all rows with the same set of values of the column, column-alias, or column-index are in the same group. For grouping, all null values for a column, column-alias, or column-index are considered equal.

If your SELECT statement contains both aggregate and non-aggregate select-expressions, all of the non-aggregate select-expressions need to be in a GROUP BY clause.

schema-name

The schema-name defines the schema in your SQL query. In IMS, the schema name is the PCB name.

table-name

The table-name defines the name of the table in your SQL query. The name must identify a segment in IMS.

table-alias

The table-alias defines the alias that is defined in your SQL query that can be used in place of the table-name.

column-name

The column-name defines the name of the column in your SQL query.

column-name

The column-name defines the name of the column in your SQL query.

'string-literal'

A 'string-literal' defines a static character string that is UTF-8 encoded.

integer-literal

An integer-literal defines an integer value within the range of −2,147,483,648 to 2,147,483,647.

decimal-literal

A decimal-literal defines a decimal value of double point precision.

SUM

The SUM function returns the sum of a set of numbers.

AVG

The AVG function returns the average of a set of numbers.

MIN

The MIN function returns the minimum value in a set of values.

MAX

The MAX function returns the maximum value in a set of values.

COUNT

The COUNT function returns the number of rows or values in a set of rows or values.

numeric functions:

ABS: The ABS function returns the absolute value of a number.
ACOS: The ACOS function returns the arc cosine of the argument as an angle, expressed in radians. The ACOS and COS functions are inverse operations.
ASIN: The ASIN function returns the arc sine of the argument as an angle, expressed in radians. The ASIN and SIN functions are inverse operations.
ATAN: The ATAN function returns the arc tangent of the argument as an angle, expressed in radians. The ATAN and TAN functions are inverse operations.
CEIL
CEILING: The CEILING function returns the smallest integer value that is greater than or equal to the argument.
COS: The COS function returns the cosine of the argument, where the argument is an angle, expressed in radians. The COS and ACOS functions are inverse operations.
COT: The COT function returns the cotangent of the argument, where the argument is an angle, expressed in radians. The COT and TAN functions are reciprocal operations.
DEGREES: The DEGREES function returns the number of degrees of the argument, which is an angle, expressed in radians.
EXP: The EXP function returns a value that is the base of the natural logarithm (e), raised to a power that is specified by the argument. The EXP and LN functions are inverse operations.
FLOOR: The FLOOR function returns the largest integer value that is less than or equal to the argument.
LN
LOG: The LN and LOG function returns the natural logarithm of the argument. The LN and EXP functions are inverse operations.
LOG10: The LOG10 function returns the common logarithm (base 10) of a number.
SIGN: The SIGN function returns an indicator of the sign of the argument.
SIN: The SIN function returns the sine of the argument, where the argument is an angle, expressed in radians.
SINH: The SINH function returns the hyperbolic sine of the argument, where the argument is an angle, expressed in radians.
SQRT: The SQRT function returns the square root of the argument.
TAN: The TAN function returns the tangent of the argument, where the argument is an angle, expressed in radians.
TANH: The TANH function returns the hyperbolic tangent of the argument, where the argument is an angle, expressed in radians.
RADIANS: The RADIANS function returns the number of radians for an argument that is expressed in degrees.
ATAN2: The ATAN2 function returns the arc tangent of x and y coordinates as an angle, expressed in radians.
MOD: The MOD function divides the first argument by the second argument and returns the remainder.
POWER: The POWER function returns the value of the first argument to the power of the second argument.

CURRENT_DATE

The CURRENT_DATE special register specifies a date that is based on a reading of the time-of-day clock when the SQL statement is executed at the application.

CURRENT_TIME

The CURRENT_TIME special register specifies a time that is based on a reading of the time-of-day clock when the SQL statement is executed at the application.

precision: The precision specifies the fractions of a second. precision can range from 0 to 12. The default precision is 3.

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP special register specifies a timestamp that is based on a reading of the time-of-day clock when the SQL statement is executed at the application.

precision: The precision specifies the fractions of a second. precision can range from 0 to 12. The default precision is 6.

LOCALTIME

The LOCALTIME special register specifies a time that is based on a reading of the time-of-day clock when the SQL statement is executed at the application.

precision: The precision specifies the fractions of a second. precision can range from 0 to 12. The default precision is 3.

LOCALTIMESTAMP

The LOCALTIMESTAMP special register specifies a timestamp that is based on a reading of the time-of-day clock when the SQL statement is executed at the application.

precision: The precision specifies the fractions of a second. precision can range from 0 to 12. The default precision is 6.

Note

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.
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

Selecting all fields with * symbol

The following statement retrieves all fields for the PATIENT table:

SELECT * 
FROM PCB01.PATIENT

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

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

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 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

For SQL for the IMS Universal JDBC driver, 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

Selecting with DISTINCT

For SQL for the IMS Universal JDBC driver, the DISTINCT keyword is supported. The following statement retrieves all distinct patient names from the PATIENT table for SQL:

SELECT DISTINCT PATNAME
FROM PCB01.PATIENT

Using the AS clause

For SQL for the IMS Universal JDBC driver, use the AS clause to rename the aggregate function column in the result set or any other field in the SELECT statement. The AS clause is not supported for SQL for COBOL application programs.

For SQL for the IMS Universal JDBC driver, the DISTINCT keyword is supported. 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

Example of SELECT using a parameter marker:

The following statement retrieves data based on the value that is supplied for the parameter for HOSPNAME:

SELECT * FROM PCB01.HOSPITAL WHERE HOSPNAME = ?

Example of using the FETCH FIRST clause:

The following statement fetches the first n number of rows returned:

SELECT HOSPNAME FROM PCB01.HOSPITAL FETCH FIRST 3 ROWS ONLY

Examples of invalid SELECT queries:

The following statement is invalid because the FROM clause is missing the WARD table:

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