Sub-Query in Columns-List of Select

.NET CORE This feature is not supported by the .NET Core implementation of the Data Provider.

The Teradata Database versions prior to 13.0 do not support a sub-query in the columns-list of a SELECT statement that returns a scalar value. When a SELECT statement that contains a scalar sub-query is executed by a version of Teradata a TdException is thrown that has the following message:

Syntax error: expected something between '(' and the 'SELECT' keyword.] [Teradata Database] [Not Specified] [2259263]

These type of statements will be generated by the Entity Provider for Teradata when a canonical function is specified in the projection of a LINQ to Entities or Entity Sql statement. The following is an example of a LINQ to Entities statement that will cause this problem to occur:

SubQuery Restriction Example	Copy Code
public void ExampleSubQueryRestriction() { // Setting up the connection string to the data provider TdConnectionStringBuilder tbuilder = new TdConnectionStringBuilder(); tbuilder.Database = "Database"; tbuilder.DataSource = "Source"; tbuilder.UserId = "user"; tbuilder.Password = "password"; EntityConnectionStringBuilder ebuilder = new EntityConnectionStringBuilder(); // Setting up the connection for the Entity Framework ebuilder.Metadata = @"res://Example/EDMExample.csdl\|res://Example/EDMExample.msl\|res://Example/EDMExample.ssdl"); ebuilder.Provider = "Teradata.Client.Provider"; ebuilder.ProviderConnectionString = tbuilder.ToString(); EDMExample context = new EDMExample(ebuilder.ToString()); var query = from o in context.Orders select new { CustomerId = o.Customers.CustomerID, CompanyName = o.Customers.CompanyName, OrderTotal = o.OrderDetails.Sum(o2 => o2.Quantity)}; foreach(var result in query) { // Process each record returned } }

SubQuery Restriction Example

Copy Code

public void ExampleSubQueryRestriction()
{
   // Setting up the connection string to the data provider
   TdConnectionStringBuilder tbuilder = new TdConnectionStringBuilder();
   tbuilder.Database = "Database";
   tbuilder.DataSource = "Source";
   tbuilder.UserId = "user";
   tbuilder.Password = "password";

   EntityConnectionStringBuilder ebuilder = new EntityConnectionStringBuilder();

   // Setting up the connection for the Entity Framework
   ebuilder.Metadata = @"res://Example/EDMExample.csdl|res://Example/EDMExample.msl|res://Example/EDMExample.ssdl");
   ebuilder.Provider = "Teradata.Client.Provider";
   ebuilder.ProviderConnectionString = tbuilder.ToString();

   EDMExample context = new EDMExample(ebuilder.ToString());

   var query = from o in context.Orders
      select new {
      CustomerId = o.Customers.CustomerID,
      CompanyName = o.Customers.CompanyName,
      OrderTotal = o.OrderDetails.Sum(o2 => o2.Quantity)};

   foreach(var result in query)
   {

       // Process each record returned

   }
}