The VALUES clause is used to define a set of rows and columns with explicit values. The clause is commonly used in temporary tables, but can also be used in view definitions. Once defined in a table or view, the output of the VALUES clause can be grouped by, joined to, and otherwise used as if it is an ordinary table - except that it can not be updated.
VALUES expression syntax
Each column defined is separated from the next using a comma. Multiple rows (which may also contain multiple columns) are separated from each other using parenthesis and a comma. When multiple rows are specified, all must share a common data type. Some examples follow:
VALUES usage examples
The next statement shall define a temporary table containing two columns and three rows. The first column will default to type integer and the second to type varchar.
Use VALUES to define a temporary table (1 of 4)
If we wish to explicitly control the output field types we can define them using the appropriate function. This trick does not work if even a single value in the target column is null.
Use VALUES to define a temporary table (2 of 4)
If any one of the values in the column th VALUESat we wish to explicitly define has a null value, we have to use the CAST expression to set the output field type:
Use VALUES to define a temporary table (3 of 4)
Alternatively, we can set the output type for all of the not-null rows in the column. DB2 will then use these rows as a guide for defining the whole column:
Use VALUES to define a temporary table (4 of 4)
More Sample SQL
Temporary tables, or (permanent) views, defined using the VALUES expression can be used much like a DB2 table. They can be joined, unioned, and selected from. They can not, however, be updated, or have indexes defined on them. Temporary tables can not be used in a sub-query.
Derive one temporary table from another
Define a view using a VALUES clause
Use VALUES defined data to seed a recursive SQL statementetc
All of the above examples have matched a VALUES statement up with a prior WITH expression, so as to name the generated columns. One doesn't have to use the latter, but if you don't, you get a table with unnamed columns, which is pretty useless:
Generate table with unnamed columns
DB2 Using SQL Related Interview Questions
|PL/SQL Interview Questions||IBM DB2 Interview Questions|
|Oracle Interview Questions||COBOL Interview Questions|
|DB2 Using SQL Interview Questions||IBM Mainframe Interview Questions|
|MYSQL DBA Interview Questions||DB2 SQL Programming Interview Questions|
|IMS/DB Interview Questions||Mainframe DB2 Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.