|
Describes the result set for a query with one row describing each output column of the query.
sa_describe_query(
query
[, add_keys ]
)
query Use this LONG VARCHAR parameter to specify the text of the SQL statement being described.
add_keys Use this optional BIT parameter to specify whether to determine a set of columns that uniquely identify rows in the result set for the query being described. The default is 0; the database server does not attempt to identify the columns. See the Remarks section below for a full explanation of this parameter.
| Column name | Data type | Description |
|---|---|---|
| column_number | INTEGER | The ordinal position of the column described by this row, starting at 1. |
| name | VARCHAR(128) | The name of the column. |
| domain_id | SMALLINT | The data type of the column. See SYSDOMAIN system view. |
| domain_name | VARCHAR(128) | The data type name. See SYSDOMAIN system view. |
| domain_name_with_size | VARCHAR(160) | The data type name, including size and precision (as used in CREATE TABLE or CAST functions). |
| width | INTEGER | The length of a string parameter, the precision of a numeric parameter, or the number of bytes of storage for any other data type. |
| scale | INTEGER | The number of digits after the decimal point for numeric data type columns, and zero for all other data types. |
| declared_width | INTEGER | The length of a string parameter, the precision of a numeric parameter, or the number of bytes of storage for any other data type. |
| user_type_id | SMALLINT | The type_id of the user-defined data type if there is one, otherwise NULL. See SYSUSERTYPE system view. |
| user_type_name | VARCHAR(128) | The name of the user-defined data type if there is one, otherwise NULL. See SYSUSERTYPE system view. |
| correlation_name | VARCHAR(128) | The correlation name associated with the expression if one is available, otherwise NULL. |
| base_table_id | UNSIGNED INTEGER | The table_id if the expression is a field, otherwise NULL. See SYSTAB system view. |
| base_column_id | UNSIGNED INTEGER | The column_id if the expression is a field, otherwise NULL. See SYSTABCOL system view. |
| base_owner_name | VARCHAR(128) | The owner name if the expression is a field, otherwise NULL. See SYSUSER system view. |
| base_table_name | VARCHAR(128) | The table name if the expression is a field, otherwise NULL. |
| base_column_name | VARCHAR(128) | The column name if the expression is a field, otherwise NULL. |
| nulls_allowed | BIT | An indicator that is 1 if the expression can be NULL, otherwise 0. |
| is_autoincrement | BIT | An indicator that is 1 if the expression is a column declared to be autoincrement, otherwise 0. |
| is_key_column | BIT | An indicator that is 1 if the expression is part of a key for the result set, otherwise 0. See the Remarks section below for more information. |
| is_added_key_column | BIT | An indicator that is 1 if the expression is an added key column, otherwise 0. See the Remarks section below for more information. |
The sa_describe_query procedure provides an API-independent mechanism to describe the name and type information for the expressions in the result set of a query.
When 1 is specified for add_keys, the sa_describe_query procedure attempts to find a set of columns from the objects being queried that, when combined, can be used as a key to uniquely identify rows in result set of the query being described. The key takes the form of one or more columns from the objects being queried, and may include columns that are not explicitly referenced in the query. If the optimizer finds a key, the column or columns used in the key are identified in the results by an is_key_column value of 1. If no key is found, an error is returned.
For any column that is included in the key but that is not explicitly referenced in the query, the is_added_key_column value is set to 1 to indicate that the column has been added to the results for the procedure; otherwise, the value of is_added_key_column is 0.
If you do not specify add_keys, or you specify a value of 0, the optimizer does not attempt to find a key for the result set, and the is_key_column and is_added_key_column columns contain NULL.
The declared_width and width values both describe the size of a column. The declared_width describes the size of the column as defined by the CREATE TABLE statement or by the query, while the width value gives the size of the field when fetched to the client. The client representation of a type may be different from the database server. For example, date and time types are converted to strings if the return_date_time_as_string option is on. For strings, fields declared with character-length semantics have a declared_width value that matches the CREATE TABLE size, while the width value gives the maximum number of bytes needed to store the returned string. For example:
| Declaration | width | declared_width |
|---|---|---|
| CHAR(10) | 10 | 10 |
| CHAR(10 CHAR) | 40 | 10 |
| TIMESTAMP | depends on the length of the timestamp format string | 8 |
| NUMERIC(10, 3) | 10 (precision) | 10 (precision) |
None
None
The following example describes the information returned when querying all columns in the Departments table:
SELECT * FROM sa_describe_query( 'SELECT * FROM Departments DEPT' );
The results show the values of the is_key_column and is_added_key_column as NULL because the add_keys parameter was not specified.
The following example describes the information returned by querying the DepartmentName and Surname columns of the Employees table, joined with the Departments table:
SELECT * FROM sa_describe_query( 'SELECT DepartmentName, Surname FROM Employees E JOIN Departments D ON E.EmployeeID = D.DepartmentHeadId', add_keys = 1 );
The results shows a 1 in rows 3 and 4 of the result set, indicating that the columns needed to uniquely identify rows in the result set for the query are Employees.EmployeeID and Departments.DepartmentID. Also, a 1 is present in the is_added_key_column for rows 3 and 4 because Employees.EmployeeID and Departments.DepartmentID were not explicitly referenced in the query being described.