Other Functions and Expressions

the {{ocient}} system supports the following miscellaneous functions and expressions case case operates similarly to conditional scripting in other programming languages, allowing it to function like an if / then / else statement or as a switch statement case sets one or more when conditions to be evaluated when a condition is evaluated as true, the database applies the specified action in a then clause if no matches are found, the database applies the action in the else clause if no else clause is specified and no matches are found, the result is null this evaluation creates a new column where the values are expressions inside the then clause syntax \ to use case like an if / then statement case \<casecondition> else expression | null end \<casecondition> = when search condition | expression then expression | null \ to use case like a switch statement case switch expression \<switchcondition> else expression | null end \<switchcondition> = when switch value then expression | null arguments description search condition a condition including a column name and a boolean expression to evaluate switch expression a column name to be used to evaluate a switch value switch value a value to be evaluated for the switch expression expression an expression to return based on evaluating the search condition or switch expression example this example uses conditional logic with case to categorize individuals into different groups based on their ages select age, case when age < 13 then 'child' when age < 19 then 'teen' else 'adult' end as age grp from sys people order by age; example this example uses switch conditions to categorize age groups select age, case age when 13 then 'thirteen' when 14 then 'fourteen' when 15 then 'fifteen' when 16 then 'sixteen' when 17 then 'seventeen' when 18 then 'eighteen' when 19 then 'nineteen' else 'not teens' end from sys people; coalesce returns the first argument that is not null or null if all arguments are null arguments must be the same type and the returned value is the most compatible type coalesce can work with mixed type numeric arguments, which means the lower precedence types are cast to the highest one for example, when using timestamp and date data types, all values are cast to a timestamp syntax coalesce(value1, value2 \[, ]) argument data type description value \[, ] all data types are supported all values must be of the same type values for comparison to find the value that is not null example select coalesce(null,null,13,null,8); output 13 commit returns the most recent commit hash of the database to which the client is currently connected syntax commit() example select commit(); output cd0d53e3aa current database alias for other functions and expressions docid\ bldix6qqhtnj mgu6lntm current node returns the name of the sql node where the current query executes the name of the node corresponds to the name column in the sys nodes system catalog table syntax current node() example select current node(); output sql0 current node id returns the identifier of the sql node where the current query executes the name of the node corresponds to the id column in the sys nodes system catalog table syntax current node id() example select current node id(); output f690c14a 4e4f 4143 8e5e 20c90b60e15c current schema returns the name of the current schema syntax current schema() example select current schema(); output schema\@database current system returns the name of the system you can set the name using the cluster and node management docid\ bnlgs0qq1wre7ndja8q0x sql statement if you never set the system name, the ocient system initializes the name of the system as a random identifier, the universally unique identifier (uuid) syntax current system() example select current system(); output 2677b88c 7f07 470c be3e e1bafddc1221 current user returns the user for the current connection syntax current user() example select current user(); output user\@database database alias for current database returns the name of the database to which the client is currently connected syntax database() example select database(); output test greatest returns the largest non null value of all the arguments, or null if all the arguments are null all values must be comparable to each other syntax greatest(value \[, ]) argument data type description value \[, ] all data types are supported all values must either be of the same type or comparable to each other a series of values to compare to find the largest value of the set example select greatest(12,13,5,8); output 13 if null returns the first argument that is not null or null if all arguments are null arguments must be the same type and the returned value is the most compatible type if null can work with mixed type numeric arguments, which means the lower precedence types are cast to the highest one for example, when using timestamp and date data types, all values are cast to a timestamp syntax if null(value1, value2) argument data type description value \[, ] all data types are supported all values must be of the same type values for comparison to find the value that is not null example select if null(null, 5) output 5 least returns the smallest non null value of all arguments, or null if all arguments are null all values must be comparable to each other syntax least(value \[, ]) argument data type description value \[, ] all data types are supported all values must either be of the same type or comparable to each other a series of values to compare to find the smallest value of the set example select least(12,13,5,8); output 5 murmur3 returns a 32 bit murmurhash3 hash of the input value as an integer data type if you specify a null input value, the function returns 0 this function is primarily useful for partitioning data syntax murmur3(value) argument data type description value all sql data types are supported specified value for the murmurhash3 hash function example select murmur3(null); output 0 example select murmur3('fred'); output 331477181 null if returns the null value if two arguments are equal; otherwise, returns the first argument syntax null if(value1, value2) example select null if(13,12); output 13 example select null if(13,13); output null show the show function lets you explore the database and its metadata for user defined items for details, see information schema docid\ zut6gcis8qdzr4spx3mh syntax show \<item>; \ \<item> can be any of the following { columns, data types, databases, groups, indexes, reserved words, schemata, tables, users, views } examples this example uses show to explore databases on the system show databases; output database name created \ test 2022 12 19 19 52 34 576426348 this example explores table data show tables; output table catalog table schema table name table type is insertable into created at \ test test schema test table base table yes 2022 12 20 14 45 13 858144104 type strip returns the sql type of the specified value syntax type strip(value) argument data type description value all data types are supported specified value for the determination of the type you can specify columns, literals, and expressions examples select type strip( r{1,2,3}); output matrix\[1]\[3] select type strip(current user()); output char select type strip(null); output null select type strip(null int\[]); output int\[] version returns the version of the database to which the client is currently connected syntax version() example select version(); output 22 0 0 20230105 2353 zn if x is null, returns 0 otherwise, returns x syntax zn(x) example select zn(null); output 0 example select zn(12); output 12 related links database, tables, views, and indexes docid\ ejutg6wjnk5eg55kizq8d system catalog database administration docid\ ib7arrqxuu1b44erzeoij