1216498 - Can Teradata functions be used in a SQL Expression Fields in Crystal Reports?

SAP Knowledge Base Article - Public

1216498 - Can Teradata functions be used in a SQL Expression Fields in Crystal Reports?

Symptom

  • Teradata functions not listed in SQL Expression Fields.
  • Native Teradata functions are not shown within the SQL Expression Editor in Crystal Reports.
  • Can native Teradata functions be used in Crystal Reports using SQL Expression Fields?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Resolution

  • Most Teradata functions can be used where appropriate in an SQL Expression Fields in Crystal Reports, although native Teradata functions may not appear in the menus of the SQL Expression Fields Editor.
         
            
  • Additional Information:
      
    • Although the Native Teradata functions do not appear in the available functions list, a large number of the functions are supported by the Teradata ODBC Driver. This means that many of the native functions will be passed through in the SELECT cause and will be processed correctly by the Teradata ODBC Driver.
        
      For example, Teradata has a function called UPPER that converts characters to uppercase. Although there is already an SQL Expression called UCASE, the native Teradata function UPPER could be used instead. By default, an SQL Expression is prefixed with "fn" because it implements an SQL function; therefore, to uppercase a field, the syntax of 
        
          {fnUCASE(mytable.lastname)}
        
      can be used, as this is a generic format that works with most ODBC drivers. Using the Teradata ODBC driver, the syntax of
         
          UPPER(mytable.lastname)
        
      will cause the Teradata ODBC driver to reference the native function. If a valid Teradata SQL Expression has been entered, Crystal Reports will allow the SQL Expression field to be saved without any errors.
        
       
    • In the case of functions that aggregate through GROUP BY (such as AVERAGE, COUNT, MINIMUM and MAXIMUM), they must be referenced directly in an SQL Command object, note in a SQL Expression Field because it requires a Group By clause, which will not be generated, and therefore will error out. This is why you need to use a Command Objects, where you write the whole SQL Query with a GROUP BY clause.

Keywords

TERADATA FUNCTION SQL EXPRESSION ODBC EDITOR FORMULA NATIVE Crystal Reports Crystal Reports Teradata Functions SQL Expression and Teradata ODBC , c2014191 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016