Add scalar function TYPEOF

Details

Detail name Value
Changelog Number 10769
Type New Feature
Status Resolved
Fix Versions Exasol 7.1.0
Resolution Date 2021-08-03

Description

We have added a new scalar function, TYPEOF which returns the SQL data type of its argument.

  • Returns a normalized version of the type. For example, if a column is defined using the data type alias INT, TYPEOF returns DECIMAL(18,0).
  • The type of any SQL expression is constant and the same for each row. Therefore, TYPEOF(expr) returns the same result for all rows, independent of the actual values of expression.
  • The return type of function TYPEOF is VARCHAR(40) UTF8.

Examples

Preparation

CREATE SCHEMA TYPEOF_EXAMPLE;
CREATE TABLE TYPE_TABLE (int_col  INT, char_col CHAR(5));
INSERT INTO TYPE_TABLE VALUES (NULL, NULL); 

Exasmple

-- Returns DECIMAL(18,0)
SELECT TYPEOF(int_col) AS type 
FROM TYPE_TABLE;

-- Returns CHAR(5) UTF8
SELECT TYPEOF(char_col) AS type 
FROM TYPE_TABLE;

-- Returns VARCHAR(40) UTF8
SELECT TYPEOF(TYPEOF(int_col)) 
FROM TYPE_TABLE;