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
  • 2
LVL 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

776 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