Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
SELECT statementUse the SELECT statement to retrieve values from a database or from an SPL or collection variable. A SELECT operation is called a query.
Projection ClauseThe Projection clause (sometimes called the Select clause) specifies a list of database objects or expressions to retrieve, and can set restrictions on qualifying rows. (The select list is sometimes also called the projection list.)
Controlling duplicate return valuesYou can apply the ALL, UNIQUE, or DISTINCT keywords to indicate whether duplicate values are returned, if any exist. If you do not specify any of these keywords in the Projection clause, all qualifying rows are returned by default.
Duplicate rows in NLSCASE INSENSITIVE databasesIn a database that was created with the NLSCASE INSENSITIVE option, columns and expressions of NCHAR or NVARCHAR data types make no distinction between upper case and lower case letters, so that strings of these data types that have the same sequence of characters, but with letter case variants, evaluate as duplicates.

Duplicate rows in NLSCASE INSENSITIVE databases

In a database that was created with the NLSCASE INSENSITIVE option, columns and expressions of NCHAR or NVARCHAR data types make no distinction between upper case and lower case letters, so that strings of these data types that have the same sequence of characters, but with letter case variants, evaluate as duplicates.

Queries that include the ALL, DISTINCT, or UNIQUE keywords might return results different from what the same query returns from a case-sensitive database into which the same character string values had been loaded. For example, the NVARCHAR strings "aCe", "ACE", and "AcE" evaluate as identical in databases that have the NLSCASE INSENSITIVE property, but the same three strings are processed as distinct values in case-sensitive databases.

Strings of type CHAR, LVARCHAR, and VARCHAR, however, are processed identically in NLSCASE SENSITIVE and in NLSCASE INSENSITIVE databases by queries that use the ALL, DISTINCT, or UNIQUE keywords to include or exclude duplicate rows. For more information about databases with the NLSCASE INSENSITIVE property, see Specifying NLSCASE case sensitivity and NCHAR and NVARCHAR expressions in case-insensitive databases.