?
Solved

Using SQL Select Statements in Crystal Reports XI

Posted on 2007-04-09
4
Medium Priority
?
19,561 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
[X]
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
4 Comments
 
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

0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

777 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