Solved

Using SQL Select Statements in Crystal Reports XI

Posted on 2007-04-09
4
19,441 Views
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?
}
0
Comment
Question by:P1ST0LPETE
  • 2
4 Comments
 
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

0
 
LVL 26

Accepted Solution

by:
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:

(
SELECT DISTINCT
  field
FROM
  table
WHERE
  condition
)

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:

(
SELECT
  AVG(answer)
FROM
  tercon.dbo.tbl_questionnaire_logins QL
  INNER JOIN tercon.dbo.tbl_questionnaire_answers QA ON QL.FIELD = QA.FIELD
WHERE
  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.

~Kurt
0
 
LVL 10

Author Comment

by:P1ST0LPETE
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.  
0
 
LVL 26

Expert Comment

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

~Kurt
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now