SAP Knowledge Base Article - Public

2345437 - Can we use a subquery in a SQL Expression Field in Crystal Reports?

Symptom

  • How to add a subquery?
  • Can we add a subquery in a SQL Expression Field in Crystal Reports?
     
    For example:
    Can we add the following SQL Query to a SQL Expression Field?
         
       Select MyField_1, MyField_2
       From My_Table
       Where MyField_1 > 10

    Or an subquery that calculate a summary like:

       Select SUM(MyField_1), MyField_2
       From My_Table
       Where MyField_1 > 10
       Group By MyField_2

Environment

  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020

Resolution

  • It is not supported to use a subquey in a SQL Expression Field.
      
  • SQL Expression Fields are added to the SELECT clause of the SQL Query generated, and can only return one value. Since a subquery have the potential of returning more than one value, it will fail. And it is not a Crystal Reports limjitation, but it is how SQL Query works when adding SQL in a SELECT clause of an SQL Query.
       
  • SQL Expression Fields are design to be used to concatenate or modify text, or to perform calculation at a data row level only, as it is executed for each row of data. For more information, see the following SAP Knowledge Base Article:
          
     1217871 - What is the intended use of 'SQL Expression Fields' in Crystal Reports?

Keywords

CR, SQL Expression , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020