Generally speaking, NULL is not a special value, but it represents an undefined value. This article describes how to work with NULL values and the types of valid comparisons
View full article
This article talks about using DECIMAL datatype to achieve more stringent results than DOUBLE datatype.
View full article
This article describes how Unicode is supported in Exasol
View full article
Question Is the ROWID always assigned in an ascending order? Is it possible to determine the sequence of inserted records with the ROWID? Answer The ROWIDs of a table are managed by the DBMS. They are assigned in an ascending order per node and stay distinct within a table. For different tables, they could be the same. DML statements such as UPDATE, DELETE, TRUNCATE or MERGE might internally reorder data storage, invalidating and reassigning all the ROWIDs. Contrary to that, structural table changes such as adding a column leave the ROWIDs unchanged. Altering   distribution keys   of or   reorganizing   a table will certainly reassign ROWID values. Therefore the ROWID can't be used to determine the exact sequence or age of a record, it is designed to be a   short term   identifier for rows to be used for duplicate elimination. The ROWID pseudo column can only be used on   table   objects, not on views or subselects. If you try it on a view, you will receive an appropriate error message Additional References ROWID Syntax
View full article
Tips on how to improve joins within Exasol.
View full article
Problem We want to replace all NULL values within a certain table with the default value specified for the according column. And we don't want to create the necessary update statements manually. Solution A small metadata-driven procedure script (Lua) that issues the required update statements. (See Attachment) Notes... ...on transactions The script performs a rollback after the metadata request, to avoid a read-write conflict scenario. performs all updates within a single transaction. will   not abort   when an update on one of the columns fails. performs a commit when all columns have been handled, regardless of any errors encountered. ...on column selection The script   includes   all columns that do have a DEFAULT value set. It   excludes   all columns with a NOT NULL constraint (ignoring the actual state of the constraint). Obviously, such a column can not contain any NULL values that need updating. ...on row selection Due to Exasol's memory management and data processing, the script handles each column separately. This minimizes both the amount of memory required for processing and the amount of data blocks being written. The script does   not   contain any delta functionality, it will process all rows of the table each time it is called. Installation Just create the script in any schema you like (CREATE SCRIPT permission required). It does not have any dependencies. create or replace /* procedure */ script REPLACE_NULL( schema_name , table_name ) ... Usage When calling the script, it expects two parameters: A schema name and a table name: execute script REPLACE_NULL( 'my schema ' , 'my table ' );   💡 Both schema and table name are expected as   string   and will be case-sensitive. Example open schema SR9000; -- Rows affected: 0 create table Invoice( invoice_id int , invoice_date date default date '2017-01-01' ); -- Rows affected: 0 insert into Invoice values (1, null ), (2, null ), (3, '2017-02-01' ); -- Rows affected: 3 execute script REPLACE_NULL( 'SR9000' , 'Invoice' ); -- [43000] " No columns found for " SR9000 "." Invoice "" caught in script " SR9000 "." REPLACE_NULL" at line 23 ( Session : 1585944483210400591) ... yes. We created the table using a regular identifier, so it ended up as uppercase... execute script REPLACE_NULL( 'SR9000' , 'INVOICE' ); This returns: COLUMN_NAME COLUMN_TYPE COLUMN_DEFAULT UPDATE_RESULT INVOICE_DATE DATE TO_DATE('2017-01-01','YYYY-MM-DD') 2 rows updated
View full article
Question How does Exasol determine the data type of a multiplication of different dataypes? Example: what data type is the result when you multiply database columns number (18,3) by number (18,9)? Can numeric overflows occur? Answer In operations with multiple operands (e.g. the operators +,-,/,*) the operands are implicitly converted to the biggest occurring data type (e.g. DOUBLE is bigger than DECIMAL) before executing the operation. This rule is also called numeric precedence. Numeric overflow can occur and result in a data exception - numeric value out of range. The result for this specific calculation would be decimal(36,12). Regularly the precision and scale were added. Examples: (12,0) * (15,3) = (27,3) (12,0) * (15,9) = (27,9) (18,3) * (18,9) = (36,12) For sums the scale is taken from the factor with the highest preciseness. (12,0) + (15,9) = (22,9) (15,3) + (15,9) = (22,9)
View full article
This article shows what happens behind the scenes when JOINing on a DECIMAL datatype.
View full article
This solution describes a method on how to retrieve type information without the need for a syntactically correct and complete CREATE statement.
View full article
Background Filter on DOUBLE columns returns unexpected results.  The DOUBLE values you see in EXAplus may differ from the actual database values due to the JDBC double handling and rendering. Explanation As the DOUBLE data type is only an approximative numeric type, filters on DOUBLE columns may return unexpected results, due to the approximative nature of this data type. The datatype DOUBLE in the Exasol DB (including 6.x) is defined as an 64-Bit floating point value which represents values with a combination of an exponent and a fraction in binary form. This means that not every existing (numeric) value can be exactly represented by this type. We recommend to filter only on DECIMAL columns to avoid described problems. Additional References The actual value range of this type can be seen in our documentation: https://docs.exasol.com/sql_references/data_types/datatypedetails.htm
View full article
Top Contributors