Using SQL Select Statements in Crystal Reports XI

Posted on 2007-04-09
Last Modified: 2008-02-06
Using Crystal Reports XI Developer;
Using MS SQL Server 2000;

public static void Main()
      Having trouble figuring out the syntax for writing SQL Expression Fields in the Formula Workshop.  My main problem is I can't find any examples anywhere to see how one actually works.  Anyway, I've checked my query on the actual SQL engine using the Query Analyzer and it works just fine there.  
My specific query looks like this:

SELECT AVG(answer)
FROM tercon.dbo.tbl_questionnaire_logins, tercon.dbo.tbl_questionnaire_answers
WHERE teamid IN (257, 258, 259)

Using the SQL statement above and clicking on the check button (in Crystals' Fomula Workshop) I get the following error:

Error in Compiling SQL Expression:
Failed to retreive data from the database.
Details: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Provider for SQL Server
Description: Incorrect syntax near the keywork 'SELECT'
SQL State" 42000
Native Error: 156 [Database Vendor Code 156 ].

I've tried all sorts of stuff.  Taking off the AVG() and just running it with "answer" in the SELECT portion.  I've tried putting parenthesis around each of the database, table and column names like Crystal does when you view Database >> Show SQL Query.  Example

SELECT "tercon"."dbo"."tbl_questionnaire_answers"."answer"
FROM "tercon"."dbo"."tbl_questionnaire_answers"

And I get the same error.  I don't know where I'm going wrong.  Any Ideas?
Question by:P1ST0LPETE
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18878517
please replace the double quotes by brackets, and I suggest to use table alias names:

SELECT qa.[answer]
FROM [tercon].dbo.[tbl_questionnaire_answers] qa

LVL 26

Accepted Solution

Kurt Reinhardt earned 500 total points
ID: 18880368
There are a couple of things going on here:

1)  A SQL Expression field is basically a SQL subquery in the SELECT clause Crystal Reports generates when it passes the query to the database (Database|Show SQL Query).  You can choose to use either the functions available in the editor (which, in turn, are based on both your database and method of connectivity) or use true SQL.  Should you choose to use SQL, you must return a distinct value and you must (for SQL Server) encapsulate the SQL in parentheses.  Here's an example of the correct syntax:


2)  In your first statement, you don't have a join or parentheses:

SELECT AVG(answer)
FROM tercon.dbo.tbl_questionnaire_logins, tercon.dbo.tbl_questionnaire_answers
WHERE teamid IN (257, 258, 259)

Instead, it should look like this:

  tercon.dbo.tbl_questionnaire_logins QL
  INNER JOIN tercon.dbo.tbl_questionnaire_answers QA ON QL.FIELD = QA.FIELD
  teamid IN (257, 258, 259)

I used an INNER JOIN in this example, since I don't know your data.

3)  There is an ODBC compatibility break with SQL Expressions and Crystal Reports XI that might affect you and prevent you from being able to create a SQL Expression even though the syntax is correct.  Here's a quote from a letter Ido Millet received from Business Objects on the matter:

Thanks to you and Linda again for sending the ODBC trace logs and reports that repro’d the problem.  Core development has investigated the problem and determined that it is a compatibility break between version 8.5 and later versions.  

I’ve entered ADAPT00718585 into our internal system to track the issue and it will be reviewed at the next bug review meeting this week.  I don’t know if we’ll be able to fix it right away, but it’s on the list for a detailed investigation / fix this month.  Depending on the complexity and risk of the resolution, we may roll it back to also apply to XI R2, XI and version 10 – to set expectations, I can’t say right now if that will happen.  I would like to fix it, as would Dev, since none of us like compatibility breaks, but I don’t like to make promises until we’ve established there are no side effects to doing a fix.

You might try choosing a different manner of connectivity (ADO, for example) or try basing the report off a view or SQL Command object if you can't get the expression to work.

LVL 10

Author Comment

ID: 18882473
Thank you Rhinok, Simply putting parentheses around my SQL statements solved my problem.  I can't explain how much of a relief it is to finally have it working.  
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 18882576
Glad I could help:)


Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question