The Entity Provider for Teradata must perform special processing of Set Operations and Case statements. The following sections describe the processing that is performed.
In a Set operation, there are two operands that consist of SELECT statements. The UNION is a commutative operation. However, the Advanced SQL Engine does not adhere to the requirements of this property. Under certain scenarios the SQL Engine can return different result sets when the operands are flipped. It can also return a result when the operands appear in a certain order, then fail when they appear in a different order.
These problems are caused when the data type of corresponding columns in the left and right operands are different. This is demonstrated in the following 2 SQL examples:
| Table Definition |
Copy Code |
|---|---|
CREATE TABLE DecimalExample(c1 INTEGER, dec1 DECIMAL(5, 2), dec2 DECIMAL(10, 4)); | |
| Example 1 |
Copy Code |
|---|---|
(SELECT c1, dec1 from DecimalExample) UNION ALL (SELECT c1, dec2 from DecimalExample) | |
| Example 2 |
Copy Code |
|---|---|
(SELECT c1, dec2 from DecimalExample UNION ALL (SELECT c1, dec1 from DecimalExample) | |
The UNION operation is commutative. However, the SQL in Example 1 will not execute because an Overflow error will be returned by Teradata. The SQL in Example 2 will execute and the (precision, scale) of the decimal column in the result set will be (10, 4). The reason that Example 1 does not execute is that the decimal in the result inherits the (precision, scale) contained in the left operand --in this case (5, 2). This causes the overflow when decimals originating from the right operand are returned.
To work around these issues, the Entity Provider must cast the column in the left operand to a precision/scale that can handle data from both operands.
When the Entity Provider processes a SET operation and generates the SELECT statement for the two operands the following tasks are performed:
A Null that appears in the column-list of the SELECT statement in either the right or left operand will be casted to the Teradata type that corresponds to column in the opposite operand.
If a column in the result set of a Set operation is a Decimal, the corresponding columns in the left and right operands will be analyzed. The column in the left operand will be casted to the Precision and Scale that can handle values returned from both columns.
The algorithm that is used to calculate the command precision and scale between the column in the left and right operands is as follows:
temp = MAX(left precision, right precision) + MAX(left scale, right scale)
precision = if temp > 38 (the Maximum Precision) the precision is 38 else it is temp.
scale = MAX(left scale, right scale)
The reason that only the column in the left operand is casted, is that the SQL Engine applies the Precision and Scale of the column in the left operand to the result set.
If a column in the result set of a Set operation is a Varchar or Char, the corresponding columns in the left and right operands will be inspected. The column in the left operand will be casted to the maximum length between the corresponding columns in the left and right operands.
If the length of the column in the left operand has the maximum length, a cast will not be specified. If it is not the maximum length, the column will be casted to the maximum length.
The reason that only the column in the left operand is casted is that the SQL Engine applies the length of the column in the left operand to the corresponding column in the result set.
The precision refers to the sub-second component.
If a column in the result set is one of these types, the corresponding columns in the left and right operands are inspected. The Entity Provider will determine the maximum precision between the columns. If the column in the left operand is the maximum precision, a cast will not be specified. If it is not the maximum, the Entity Provider will cast the column to the maximum precision.
In the SQL Engine there are several different types of Intervals. In terms of the Entity Provider, the Intervals can be broken into two different categories:
EDM.Time is one of the EDM types that map to several different Teradata Types (refer to Data Type Mappings for more information). The Interval types that map to this EDM type are:
The Interval types that have a Second component (bolded Intervals) also have a Sub-Second component. These types of Intervals contain two precisions. One precision describes the range of the interval, the second describes the sub-seconds or fractional seconds that can be specified for the interval. An example of an Interval definition is:
| Interval Precision Example |
Copy Code |
|---|---|
INTERVAL DAY (3) TO SECOND (4) ex. 100 12:43:21.3431 | |
In this example, the maximum number of days that can be specified is 999 (the range) and the number of digits in the sub-seconds is 4.
The other Intervals only contains a precision that represents the range. An example of this is:
| Interval Max Days Example |
Copy Code |
|---|---|
INTERVAL DAY(4) TO HOUR ex. 4322 10 | |
The maximum number of days that can be specified in this example is 9999.
Behavior of Entity Provider
In the Entity Provider, the precision represents the sub-seconds. When one of these Intervals is specified in a Set operation the maximum precision between the corresponding columns is determined. If the column on the left operand is not the maximum precision, the Entity Provider will cast the column to the maximum precision. The Entity Provider will always cast the range to the maximum which is 4.
The information that is collected by the Entity Framework when processing a LINQ to Entities or Entity Sql statement will not include the range of an Interval. Therefore, the precision is not defined for Intervals that only have a range. However, the range will always be casted to the maximum value of 4.
EDM.String is another EDM type that maps to several different Teradata Types (refer to Data Type Mappings for more information). These intervals are represented as a EDM.String because they cannot be represented as EDM.Time. The Interval types that map to this EDM type are:
They only contain one precision that represents the range. An example of this is:
| Interval Year-Month Example |
Copy Code |
|---|---|
INTERVAL YEAR(2) ex. 32 | |
In this example the range is 2. Therefore, the maximum number of years that can be specified is 99.
Behavior of Entity Provider
Since these intervals are mapped to EDM.String, the information that is returned by the Entity Framework is the maximum length (MaxLength) of the string. The Entity Provider is able to determine the range of these intervals from the MaxLength.
When one of these Intervals is specified in the a Set operation the maximum range between the corresponding columns is determined. If the column on the left operand is not the maximum range, the Entity Provider will cast the column to the maximum.
If the information in the Command Tree specifies a Null literal in the THEN or ELSE clause of a CASE statement, the Entity Provider will cast the Null to the Teradata type that is to be returned from the CASE.
In some situations, the Entity Framework will indicate that a scalar sub-query is to be specified in the WHEN clause of a CASE statement. The SQL Engine only supports this capability in versions 13.0 or greater. The Entity Provider will throw a NotSupportedException if a scalar sub-query is to be specified in a CASE satement when connected to a Teradata Database version earlier than 13.0.