Sorting data

Abstract

This article explains the ways to sort data returned by a SQL query, using substitution variables as well as the DEFINE and VERIFY commands.

Sorting

The default sort order, for the data returned by a SQL query, is ascending. In this sort order:

• numeric values are displayed with the lowest values first; for example, 1 to 999
• date values are displayed with the earliest value first; for example, 01-JAN-92 before 01-JAN-95
• character values are displayed in the alphabetical order; for example, "A" first and "Z" last
• null values are displayed last for ascending sequences and first for descending sequences
• you can also sort by a column that is not in the SELECT list

Here are some examples that depict sorting:

1. To reverse the order in which the rows are displayed, specify the DESCkeyword after the column name in the ORDER BY clause. This sample code sorts the result by the most recently hired employee.
   
   SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
2. You can also use a column alias in the ORDER BY clause. This sample code sorts the data by annual salary.
   
   SELECT employee_id, last_name, salary*12 annual
FROM employees
ORDER BY annual ;
3. You can sort query results by specifying the numeric position of the column in the SELECT clause. This sample code sorts the result bydepartment_id, as this column is at the third position in the SELECTclause.
   
   SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY 3;
4. You can sort query results by more than one column. The sort limit is the number of columns in the given table. In the ORDER BY clause, you specify the columns and separate the column names using commas. If you want to reverse the order of a column, specify DESC after its name, as in this sample code.
   
   SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

Substitution variables

So far, all SQL statements were executed with predetermined columns, conditions, and their values. Suppose you want a query that lists the employees with various job - ids and not just those with job_ID as SA_REP. In such a scenario, you can edit the WHEREclause to provide a different value each time you run the command.

Figure 1: Substitution variables

An alternative way, to run the same query for different values, is by using a substitution variable in place of the exact values in the WHERE clause. Using this alternative method, you can create reports that prompt users to supply their own values. This restricts the range of data returned.

You can embed substitution variables in a command file or in a single SQL statement. A variable can be thought of as a container in which values are temporarily stored. When the statement is run, the stored value is substituted.

You can use single-ampersand (&) substitution variables to temporarily store values. You can also predefine variables by using the DEFINE command. The DEFINE command creates and assigns a value to a variable.

These are some examples of restricted ranges of data:

• reporting figures only for the current quarter or specified date range
• reporting on data relevant only to the user requesting the report
• displaying personnel only within a given department

Interactive effects are not restricted to direct user interaction with the WHERE clause. The same principles can be used to achieve other goals, such as

• obtaining input values from a file rather than from a person
• passing values from one SQL statement to another

Note : Both SQL Developer and SQL* Plus support substitution variables and the DEFINE/UNDEFINE commands. SQL Developer or SQL* Plus do not support validation checks, except for data type, on user input.

Using the single-ampersand substitution variable

When running a report, users often want to restrict dynamically returned data. SQL*Plus or SQL Developer provides this flexibility with user variables. You use an ampersand to identify each variable in your SQL statement – however, you do not need to define the value for each variable.

The &user_variable notation indicates a variable in a SQL statement. If the variable does not exist, SQL*Plus or SQL Developer prompts the user for a value. The new variable is discarded after it is used.

This sample code creates a SQL Developer substitution variable for an employee number. When the statement is executed, SQL Developer prompts the user for an employee number and then displays the employee number, last name, salary, and department number for that employee. With the single ampersand, the user is prompted every time the command is executed, if the variable does not exist.

• SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;

Figure 2: Single-ampersand substitution variables

When SQL Developer detects that the SQL statement contains an ampersand, you are prompted to enter a value for the substitution variable named in the SQL statement. After you enter a value and click OK, results are displayed in the Results tab of your SQL Developer session.

Figure 3: Single-ampersand substitution variable output

Character and date values with substitution variables

In a WHERE clause, date and character values must be enclosed within single quotation (') marks. The same rule applies to the substitution variables. You enclose the variable within single quotation marks within the SQL statement itself.

This sample query retrieves employee names, department numbers, and annual salaries of all employees based on the job title value of the SQL Developer substitution variable.

SELECT last_name, department_id, salary*12
FROM employees
WHERE job_id = '&job_title' ;

Figure 4: Character and date values with substitution variables

Specifying column names, expressions, and text

You can use substitution variables not only in the WHERE clause of a SQL statement, but also as a substitute for column names, expressions, or text.

This sample code displays the employee number, last name, job title, and any other column that is specified by the user at run time, from the EMPLOYEES table. For each substitution variable in the SELECT statement, you are prompted to enter a value, and then click OK to proceed. If you do not enter a value for the substitution variable, you get an error when you execute the preceding statement.

SELECT employee_id, last_name, job_id,&column_name
FROM employees
WHERE &condition
ORDER BY &order_column ;

Figure 5: Column Names, Expressions, and Text

Note : A substitution variable can be used anywhere in the SELECT statement, except as the first word entered at the command prompt.

Using the double-ampersand substitution variable

You can use the double-ampersand (&&) substitution variable if you want to reuse the variable value without prompting the user each time. The user sees the prompt for the value only once. In this sample code, the user is asked to give the value for the variable,column_name, only once. The value, department_id that is supplied by the user is used for displaying and ordering data. If you run the query again, you will not be prompted for the value of the variable.

SELECT employee_id, last_name, job_id, &&column_name
FROM employees
ORDER BY &column_name ;

Figure 6: Double-ampersand substitution variable

SQL Developer stores the value supplied by using the DEFINE command; it uses the value again whenever you reference the variable name. After a user variable is defined, you use the UNDEFINE command to delete it.

This is the syntax for the UNDEFINE command.

UNDEFINE column_name

Using the DEFINE command

This sample code creates a substitution variable for an employee number by using theDEFINE command. At run time, the code displays the employee number, name, salary, and department number for that employee.

DEFINE employee_num = 200

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;

UNDEFINE employee_num

Because the variable is created using the SQL Developer DEFINE command, the user is not prompted to enter a value for the employee number. Instead, the defined variable value is automatically substituted in the SELECT statement. The EMPLOYEE_NUM substitution variable is present in the session until the user undefines it, or exits the SQL Developer session.

Using the VERIFY command

To confirm the changes in a SQL statement, you use the VERIFY command. Setting SET VERIFY ON forces SQL Developer to display the text of a command after it replaces substitution variables with values. To see the VERIFY output, you should use the Run Script icon or press the F5 key in the SQL Worksheet.

SQL Developer displays the text of a command after it replaces substitution variables with values, in the Script Output tab. In this sample code, the new value of the EMPLOYEE_IDcolumn in the SQL statement is displayed followed by the output.

SET VERIFY ON
SELECT employee_id, last_name, salary
FROM employees
WHERE employee_id = &employee_num;

Figure 7: VERIFY command

SQL*Plus system variables

SQL*Plus uses various system variables that control the working environment. One of the variables is VERIFY. To obtain a complete list of all the system variables, you can issue the SHOW ALL command on the SQL*Plus command prompt.