Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using SQL Select Statements in Crystal Reports XI

Posted on 2007-04-09
4
Medium Priority
?
19,600 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

604 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