github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-value.md (about) 1 --- 2 layout: default 3 title: Values - Reference Manual - csvq 4 category: reference 5 --- 6 7 # Values 8 9 * [Primitive Types](#primitive_types) 10 * [Expressions that can be used as a value](#expressions) 11 * [Automatic Type Casting](#automatic_type_casting) 12 13 ## Primitive Types 14 {: #primitive_types} 15 16 ### String 17 {: #string} 18 19 Character strings encoded in UTF-8. 20 21 ### Integer 22 {: #integer} 23 24 64-bit signed integers. 25 26 ### Float 27 {: #float} 28 29 64-bit floating point numbers. 30 31 ### Boolean 32 {: #boolean} 33 34 Boolean values. true or false. 35 36 ### Ternary 37 {: #ternary} 38 39 Values of three-valued logic. TRUE, UNKNOWN or FALSE. 40 41 ### Datetime 42 {: #datetime} 43 44 Values of Date and time with nanoseconds. 45 46 ### Null 47 {: #null} 48 49 Representations of missing values. 50 51 > NULL is not a state indicating that a value does not exist or UNKNOWN, but a value of a different type from UNKNOWN. 52 > Empty cells are set to NULL, and UNKNOWN is used as the results of the operator evaluations. 53 > 54 > Note that `NULL IS UNKNOWN` evaluates to `TRUE`, but `UNKNOWN IS NULL` evaluates to `FALSE`. 55 56 ## Expressions that can be used as a value 57 {: #expressions} 58 59 * [Field Reference](#field_reference) 60 * [Arithmetic Operation](#arithmetic_operation) 61 * [String Operation](#string_operation) 62 * [Function](#function) 63 * [Subquery](#subquery) 64 * [Variable](#variable) 65 * [Variable Substitution](#variable_substitution) 66 * [Environment Variable](#environment-variable) 67 * [Runtime Information](#runtime-information) 68 * [System Defined Constant](#system-defined-constant) 69 * [Flag](#flag) 70 * [Parentheses](#parentheses) 71 * [Case Expressions](#case) 72 * [Comparison Operation](#comparison_operation) 73 * [Logic Operation](#logic_operation) 74 * [Cursor Status](#cursor_status) 75 76 77 ### Field Reference 78 {: #field_reference} 79 80 References to fields in tables. 81 82 A field reference is represented by a column name or a table name and a column name joined with a full stop(U+002E). 83 Fields can also be referred with column numbers in each table starting with 1. 84 85 ```sql 86 field_reference 87 : column_name 88 | table_name.column_name 89 | table_name.column_number 90 ``` 91 92 _table_name_ 93 : [identifier]({{ '/reference/statement.html#parsing' | relative_url }}) 94 95 _column_name_ 96 : [identifier]({{ '/reference/statement.html#parsing' | relative_url }}) 97 98 _column_number_ 99 : [integer]({{ '/reference/value.html#integer' | relative_url }}) 100 101 102 ### Arithmetic Operation 103 {: #arithmetic_operation} 104 105 [Arithmetic Operators]({{ '/reference/arithmetic-operators.html' | relative_url }}) 106 107 ### String Operation 108 {: #string_operation} 109 110 [String Operators]({{ '/reference/string-operators.html' | relative_url }}) 111 112 ### Function 113 {: #function} 114 115 * [Logical Functions]({{ '/reference/logical-functions.html' | relative_url }}) 116 * [Numeric Functions]({{ '/reference/numeric-functions.html' | relative_url }}) 117 * [DateTime Functions]({{ '/reference/datetime-functions.html' | relative_url }}) 118 * [String Functions]({{ '/reference/string-functions.html' | relative_url }}) 119 * [Cryptographic Hash Functions]({{ '/reference/cryptographic-hash-functions.html' | relative_url }}) 120 * [Cast Functions]({{ '/reference/cast-functions.html' | relative_url }}) 121 * [System Functions]({{ '/reference/system-functions.html' | relative_url }}) 122 * [Aggregate Functions]({{ '/reference/aggregate-functions.html' | relative_url }}) 123 * [Analytic Functions]({{ '/reference/analytic-functions.html' | relative_url }}) 124 * [User Defined Functions]({{ '/reference/user-defined-function.html' | relative_url }}) 125 126 ### Subquery 127 {: #subquery} 128 129 [Select Query]({{ '/reference/select-query.html' | relative_url }}) enclosed in parentheses. 130 131 A result set of a subquery must have exactly one field and at most one record. 132 If the result set has no record, that subquery returns null. 133 134 ### Variable 135 {: #variable} 136 137 [Variable]({{ '/reference/variable.html' | relative_url }}) 138 139 ### Variable Substitution 140 {: #variable_substitution} 141 142 [Variable Substitution]({{ '/reference/variable.html#substitution' | relative_url }}) 143 144 ### Environment Variable 145 {: #environment-variable} 146 147 [Environment Variable]({{ '/reference/environment-variable.html' | relative_url }}) 148 149 ### Runtime Information 150 {: #runtime-information} 151 152 [Runtime Information]({{ '/reference/runtime-information.html' | relative_url }}) 153 154 ### System Defined Constant 155 {: #system-defined-constant} 156 157 [System Defined Constant]({{ '/reference/system-defined-constant.html' | relative_url }}) 158 159 ### Flag 160 {: #flag} 161 162 [Flag]({{ '/reference/flag.html' | relative_url }}) 163 164 ### Parentheses 165 {: #parentheses} 166 167 Values enclosed in parentheses. 168 169 Parentheses are used to specify the order of operations. 170 171 ### Case Expressions 172 {: #case} 173 174 #### Case syntax with condition 175 176 ```sql 177 CASE 178 WHEN condition THEN result 179 [WHEN condition THEN result ...] 180 [ELSE result] 181 END 182 ``` 183 184 _condition_ 185 : value 186 187 _result_ 188 : value 189 190 This syntax returns the _result_ of the first WHEN expression that _condition_ is TRUE. 191 If no condition is TRUE, then returns the _result_ of the ELSE expression or a null if there is no ELSE expression. 192 193 194 #### Case syntax with comparison 195 196 ```sql 197 CASE value 198 WHEN comparison_value THEN result 199 [WHEN comparison_value THEN result ...] 200 [ELSE result] 201 END 202 ``` 203 204 _value_ 205 : value 206 207 _comparison_value_ 208 : value 209 210 _result_ 211 : value 212 213 This syntax returns the _result_ of the first WHEN expression that _comparison_value_ is equal to _value_. 214 If no _comparison_value_ is match, then returns the _result_ of the ELSE expression or a null if there is no ELSE expression. 215 216 ### Comparison Operation 217 {: #comparison_operation} 218 219 [Comparison Operators]({{ '/reference/comparison-operators.html' | relative_url }}) 220 221 ### Logic Operation 222 {: #logic_operation} 223 224 [Logic Operators]({{ '/reference/logic-operators.html' | relative_url }}) 225 226 ### Cursor Status 227 {: #cursor_status} 228 229 [Cursor Status]({{ '/reference/cursor.html#status' | relative_url }}) 230 231 232 ## Automatic Type Casting 233 {: #automatic_type_casting} 234 235 Every Value has a primitive type. 236 A value is converted to another primitive type as necessary. 237 For example, in arithmetic operations, both left-hand side value and right-hand side value are converted to integer or float values. 238 If the conversion fails, then the value is converted to null. 239 240 Field values are imported as strings from csv. 241 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. 242 243 244 | Conversion to | Type | Value | Value after conversion | 245 |:--------------|:---------|:-----------------------------------------------------------------------|:------------------------------------------------| 246 | String | Integer | | String representing the decimal integer | 247 | | Float | | String representing the floating-point decimal | 248 | | Datetime | | Null | 249 | | Boolean | | Null | 250 | | Ternary | | Null | 251 | | Null | | Null | 252 | Integer | String | Representation of a decimal integer | Integer represented by the string | 253 | | | Other values | Null | 254 | | Float | | Null | 255 | | Datetime | | Null | 256 | | Boolean | | Null | 257 | | Ternary | | Null | 258 | | Null | | Null | 259 | Float | String | Representation of a floating-point decimal or its exponential notation | Float represented by the string | 260 | | | 'Inf', '+Inf' | +Inf | 261 | | | '-Inf' | -Inf | 262 | | | 'NaN' | NaN | 263 | | | Other values | Null | 264 | | Integer | | Float equivalent to the integer | 265 | | Datetime | | Null | 266 | | Boolean | | Null | 267 | | Ternary | | Null | 268 | | Null | | Null | 269 | Datetime | String | Datetime Formats | Datetime represented by the string | 270 | | | Other values | Null | 271 | | Integer | | Null | 272 | | Float | | Null | 273 | | Boolean | | Null | 274 | | Ternary | | Null | 275 | | Null | | Null | 276 | Boolean | String | '1', 't', 'true' | true | 277 | | | '0', 'f', 'false' | false | 278 | | | Other values | Null | 279 | | Integer | 1 | true | 280 | | | 0 | false | 281 | | | Other values | Null | 282 | | Float | 1 | true | 283 | | | 0 | false | 284 | | | Other values | Null | 285 | | Datetime | | Null | 286 | | Ternary | TRUE | true | 287 | | | FALSE | false | 288 | | | UNKNOWN | Null | 289 | | Null | | Null | 290 | Ternary | String | '1', 't', 'true' | TRUE | 291 | | | '0', 'f', 'false' | FALSE | 292 | | | Other values | UNKNOWN | 293 | | Integer | 1 | TRUE | 294 | | | 0 | FALSE | 295 | | | Other values | UNKNOWN | 296 | | Float | 1 | TRUE | 297 | | | 0 | FALSE | 298 | | | Other values | UNKNOWN | 299 | | Datetime | | UNKNOWN | 300 | | Boolean | true | TRUE | 301 | | | false | FALSE | 302 | | Null | | UNKNOWN | 303 304 305 > String formats representing [Datetime]({{ '/reference/cast-functions.html#format-of-string-as-datetime' | relative_url }})