SQL/MP Query Guide

Retrieving Data: How to Write Queries

HP NonStop SQL/MP Query Guide—524488-003

1-28

Specifying Date-Time Values in Programs

In SELECT, UPDATE, DELETE, and SELECT with INSERT statements, date values

can be specified in different ways, depending on whether the date value is a date literal

or a parameter or host variable in a program.

For a literal, a date-time value is specified as follows:

DATETIME "09-15-1994" YEAR TO DAY

For a parameter, a date-time data type is specified as follows:

?date TYPE AS DATE

Defining Subqueries

SQL supports the construct of nested queries or subqueries. A subquery is a SELECT

statement that appears within the body of another expression, such as a SELECT,

UPDATE, or DELETE statement, and selects an element for comparison purposes. For

example, a subquery can appear in the ON, WHERE, or HAVING clause of a SELECT

statement (called the outer query).

A subquery selects an element for the purpose of comparison. A subquery is one way

to relate data in more than one table. For example, suppose that you want to find all

employees whose salary is greater than the average salary of all employees. Logically,

this information can be derived from two separate queries:

SELECT AVG(SALARY)

FROM EMPLOYEE ;

The first query returns this result:

(EXPR)

--------

48784.65

--- 1 row(s) selected.

Now enter the second query, substituting the value retrieved from the first query:

SELECT LAST_NAME, FIRST_NAME, SALARY

FROM EMPLOYEE

WHERE SALARY > 48784.65 ;

The query returns this result:

LAST_NAME FIRST_NAME SALARY

------------- ------------- ----------

GREEN ROGER 175500.00

. . .

HENDERSON BEN 65000.00

--- 17 row(s) selected.