---
layout: default
title: Values - Reference Manual - csvq
category: reference
---

# Values

* [Primitive Types](#primitive_types)
* [Expressions that can be used as a value](#expressions)
* [Automatic Type Casting](#automatic_type_casting)

## Primitive Types
{: #primitive_types}

### String
{: #string}

Character strings encoded in UTF-8.

### Integer
{: #integer}

64-bit signed integers.

### Float
{: #float}

64-bit floating point numbers.

### Boolean
{: #boolean}

Boolean values. true or false.

### Ternary
{: #ternary}

Values of three-valued logic. TRUE, UNKNOWN or FALSE.

### Datetime
{: #datetime}

Values of Date and time with nanoseconds.

### Null
{: #null}

Representations of missing values.

> NULL is not a state indicating that a value does not exist or UNKNOWN, but a value of a different type from UNKNOWN.
> Empty cells are set to NULL, and UNKNOWN is used as the results of the operator evaluations.
>
> Note that `NULL IS UNKNOWN` evaluates to `TRUE`, but `UNKNOWN IS NULL` evaluates to `FALSE`.

## Expressions that can be used as a value
{: #expressions}

* [Field Reference](#field_reference)
* [Arithmetic Operation](#arithmetic_operation)
* [String Operation](#string_operation)
* [Function](#function)
* [Subquery](#subquery)
* [Variable](#variable)
* [Variable Substitution](#variable_substitution)
* [Environment Variable](#environment-variable)
* [Runtime Information](#runtime-information)
* [System Defined Constant](#system-defined-constant)
* [Flag](#flag)
* [Parentheses](#parentheses)
* [Case Expressions](#case)
* [Comparison Operation](#comparison_operation)
* [Logic Operation](#logic_operation)
* [Cursor Status](#cursor_status)


### Field Reference
{: #field_reference}

References to fields in tables.

A field reference is represented by a column name or a table name and a column name joined with a full stop(U+002E).
Fields can also be referred with column numbers in each table starting with 1. 

```sql
field_reference
  : column_name
  | table_name.column_name
  | table_name.column_number
```

_table_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

_column_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

_column_number_
: [integer]({{ '/reference/value.html#integer' | relative_url }})


### Arithmetic Operation
{: #arithmetic_operation}

[Arithmetic Operators]({{ '/reference/arithmetic-operators.html' | relative_url }})

### String Operation
{: #string_operation}

[String Operators]({{ '/reference/string-operators.html' | relative_url }})

### Function
{: #function}

* [Logical Functions]({{ '/reference/logical-functions.html' | relative_url }})
* [Numeric Functions]({{ '/reference/numeric-functions.html' | relative_url }})
* [DateTime Functions]({{ '/reference/datetime-functions.html' | relative_url }})
* [String Functions]({{ '/reference/string-functions.html' | relative_url }})
* [Cryptographic Hash Functions]({{ '/reference/cryptographic-hash-functions.html' | relative_url }})
* [Cast Functions]({{ '/reference/cast-functions.html' | relative_url }})
* [System Functions]({{ '/reference/system-functions.html' | relative_url }})
* [Aggregate Functions]({{ '/reference/aggregate-functions.html' | relative_url }})
* [Analytic Functions]({{ '/reference/analytic-functions.html' | relative_url }})
* [User Defined Functions]({{ '/reference/user-defined-function.html' | relative_url }})

### Subquery
{: #subquery}

[Select Query]({{ '/reference/select-query.html' | relative_url }}) enclosed in parentheses.

A result set of a subquery must have exactly one field and at most one record.
If the result set has no record, that subquery returns null.

### Variable
{: #variable}

[Variable]({{ '/reference/variable.html' | relative_url }})

### Variable Substitution
{: #variable_substitution}

[Variable Substitution]({{ '/reference/variable.html#substitution' | relative_url }})

### Environment Variable
{: #environment-variable}

[Environment Variable]({{ '/reference/environment-variable.html' | relative_url }})

### Runtime Information
{: #runtime-information}

[Runtime Information]({{ '/reference/runtime-information.html' | relative_url }})

### System Defined Constant
{: #system-defined-constant}

[System Defined Constant]({{ '/reference/system-defined-constant.html' | relative_url }})

### Flag
{: #flag}

[Flag]({{ '/reference/flag.html' | relative_url }})

### Parentheses
{: #parentheses}

Values enclosed in parentheses.

Parentheses are used to specify the order of operations.

### Case Expressions
{: #case}

#### Case syntax with condition

```sql
CASE
  WHEN condition THEN result
  [WHEN condition THEN result ...]
  [ELSE result]
END
```

_condition_
: value

_result_
: value

This syntax returns the _result_ of the first WHEN expression that _condition_ is TRUE.
If no condition is TRUE, then returns the _result_ of the ELSE expression or a null if there is no ELSE expression.


#### Case syntax with comparison

```sql
CASE value
  WHEN comparison_value THEN result
  [WHEN comparison_value THEN result ...]
  [ELSE result]
END
```

_value_
: value

_comparison_value_
: value

_result_
: value

This syntax returns the _result_ of the first WHEN expression that _comparison_value_ is equal to _value_.
If no _comparison_value_ is match, then returns the _result_ of the ELSE expression or a null if there is no ELSE expression.

### Comparison Operation
{: #comparison_operation}

[Comparison Operators]({{ '/reference/comparison-operators.html' | relative_url }})

### Logic Operation
{: #logic_operation}

[Logic Operators]({{ '/reference/logic-operators.html' | relative_url }})

### Cursor Status
{: #cursor_status}

[Cursor Status]({{ '/reference/cursor.html#status' | relative_url }})


## Automatic Type Casting
{: #automatic_type_casting}

Every Value has a primitive type. 
A value is converted to another primitive type as necessary.
For example, in arithmetic operations, both left-hand side value and right-hand side value are converted to integer or float values.
If the conversion fails, then the value is converted to null.

Field values are imported as strings from csv.
You can cast value types expressly by using [cast functions]({{ '/reference/cast-functions.html' | relative_url }}), and the result is slightly different from an automatic conversion.


| Conversion to | Type     | Value                                                                  | Value after conversion                          |
|:--------------|:---------|:-----------------------------------------------------------------------|:------------------------------------------------|
| String        | Integer  |                                                                        | String representing the decimal integer         |
|               | Float    |                                                                        | String representing the floating-point decimal  |
|               | Datetime |                                                                        | Null                                            |
|               | Boolean  |                                                                        | Null                                            |
|               | Ternary  |                                                                        | Null                                            |
|               | Null     |                                                                        | Null                                            |
| Integer       | String   | Representation of a decimal integer                                    | Integer represented by the string               |
|               |          | Other values                                                           | Null                                            |
|               | Float    |                                                                        | Null                                            |
|               | Datetime |                                                                        | Null                                            |
|               | Boolean  |                                                                        | Null                                            |
|               | Ternary  |                                                                        | Null                                            |
|               | Null     |                                                                        | Null                                            |
| Float         | String   | Representation of a floating-point decimal or its exponential notation | Float represented by the string                 |
|               |          | 'Inf', '+Inf'                                                          | +Inf                                            |
|               |          | '-Inf'                                                                 | -Inf                                            |
|               |          | 'NaN'                                                                  | NaN                                             |
|               |          | Other values                                                           | Null                                            |
|               | Integer  |                                                                        | Float equivalent to the integer                 |
|               | Datetime |                                                                        | Null                                            |
|               | Boolean  |                                                                        | Null                                            |
|               | Ternary  |                                                                        | Null                                            |
|               | Null     |                                                                        | Null                                            |
| Datetime      | String   | Datetime Formats                                                       | Datetime represented by the string              |
|               |          | Other values                                                           | Null                                            |
|               | Integer  |                                                                        | Null                                            |
|               | Float    |                                                                        | Null                                            |
|               | Boolean  |                                                                        | Null                                            |
|               | Ternary  |                                                                        | Null                                            |
|               | Null     |                                                                        | Null                                            |
| Boolean       | String   | '1', 't', 'true'                                                       | true                                            |
|               |          | '0', 'f', 'false'                                                      | false                                           |
|               |          | Other values                                                           | Null                                            |
|               | Integer  | 1                                                                      | true                                            |
|               |          | 0                                                                      | false                                           |
|               |          | Other values                                                           | Null                                            |
|               | Float    | 1                                                                      | true                                            |
|               |          | 0                                                                      | false                                           |
|               |          | Other values                                                           | Null                                            |                                                                                                                                                                                                                                                                      
|               | Datetime |                                                                        | Null                                            |
|               | Ternary  | TRUE                                                                   | true                                            |
|               |          | FALSE                                                                  | false                                           |
|               |          | UNKNOWN                                                                | Null                                            |
|               | Null     |                                                                        | Null                                            |
| Ternary       | String   | '1', 't', 'true'                                                       | TRUE                                            |
|               |          | '0', 'f', 'false'                                                      | FALSE                                           |
|               |          | Other values                                                           | UNKNOWN                                         |
|               | Integer  | 1                                                                      | TRUE                                            |
|               |          | 0                                                                      | FALSE                                           |
|               |          | Other values                                                           | UNKNOWN                                         |
|               | Float    | 1                                                                      | TRUE                                            |
|               |          | 0                                                                      | FALSE                                           |
|               |          | Other values                                                           | UNKNOWN                                         |
|               | Datetime |                                                                        | UNKNOWN                                         |
|               | Boolean  | true                                                                   | TRUE                                            |
|               |          | false                                                                  | FALSE                                           |
|               | Null     |                                                                        | UNKNOWN                                         |


> String formats representing Datetime]({{ '/reference/cast-functions.html#format-of-string-as-datetime' | relative_url }})