CASESPECIFIC Teradata

Compatibility: Teradata Extension

The CASESPECIFIC attribute may be used to request that Teradata compare data values with a distinction made between upper and lower case. The logic behind this designation is that even in Teradata mode, case sensitivity can be requested to make the SQL work the same as ANSI mode, which is case specific. Therefore, when CASESPECIFIC is used, it normally appears in the WHERE clause.

The syntax of the next two statements execute exactly the same:

Conversely, if ANSI is the current mode and there is a need for it to be non-case specific, the NOT can be used to adjust the default operation of the SQL within a mode.

The following SQL forces ANSI to be non-case specific:

Or, it may be abbreviated as:

The next SELECT demonstrates the functionality of CASESPECIFIC and CS for comparing an equality condition like it executed above in ANSI mode:

No Rows Returned

No rows are returned, because ‘A’ is different than ‘a’ when case sensitivity is used. At first glance, this seems to be unnecessary since the mode can be set to use either ANSI or Teradata. However, the dot (.) commands are BTEQ commands. They do not work in Queryman. If case sensitivity is needed when using other tools, this is one of the options available to mimic ANSI comparisons while in Teradata mode.

The SQL extensions in Teradata may be used to eliminate the absolute need to log off to reset the mode and then log back onto Teradata in order to use a characteristic like case sensitivity. Instead, Teradata mode can be forced to use a case specific comparison, like ANSI mode by incorporating the CASESPECIFIC (CS) into the SQL. The case specific option is not a statement level feature; it must be specified for each column needing this type of comparison in both BTEQ and Queryman.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics