Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL expressions not working in CR 8.5 and freezing in 11

Status
Not open for further replies.

Planchardo

Programmer
Nov 29, 2006
2
JM
I have been trying to write an SQL query in a report in CR 8.5 and 11, but having problems in both. Here is the query:

(select employee.memberid from employee
where DateDiff("m",EMPLOYEE.dateOfBirth,{fn CURDATE()}) > 462
and DateDiff("m",EMPLOYEE.dateOfBirth,{fn CURDATE()}) <= 522
and EMPLOYEE.sex <> 'F'
and COVERAGEMSTR.groupNo = '0000094001'
and PRODUCT.productId in ('GAD&D', 'GLIFE'))

Basically, I am trying to select the member IDs of employees in a certain age range with certain criteria, and pass that list of IDs into a running total. The best way I figured was to use an SQL query, but every time the syntax is checked in 8.5, I keep on getting the error message

"Error in compiling SQL expression : . Error in initialising SQL Expression parser. Error in creating SQL Expression COM parser.",

no matter how simple the expression (such as (select * from employee)). Even when I make a new report and have only the sql expression field in the report, I still get the same error when I check the syntax.

So I tried the same expression in CR 11, and the syntax apparently works, but once I put the sql expression field in the report itself and try to preview it, the program keeps on freezing. Also, when I try to break down the query into something simple, I get an error along the lines of

"Error in compiling SQL expression:"..." Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression."

This message usually only shows up if I search on just one table, or if the criteria searches on the same table

eg. (select employee.memberid from employee where EMPLOYEE.lastname = 'gordon'),

...but if I add criteria that searches other tables , the syntax works:

eg. (select employee.memberid from employee where EMPLOYEE.lastname = 'gordon' and product.groupno = '94001').

However, the program still freezes once I try to preview the report. Is there any solution to this problem with both 8.5 and 11? And if not, is it possible for me to use a formula to do what I need to do?
 
SQL expressions are not really meant as a selection tool. You can do some limited subqueries, but a SQL expression cannot return a set of results, which is what it looks like you're trying to do. The functionality also changed by CR XI, and in XI, SQL queries are developed in commands (database expert->your datasource->add command).

I think you should explain what you are trying to do. You can define selected conditions in a running total by using the "evaluate using a formula" section.

-LB
 
Thanks very much for the reply, but I eventually figured out a way to get what I wanted done without using an SQL expression at all. But thanks as well for the tip on using the SQL command option, didn't know about that.
 
I don't think the implementation of SQL Expressions has changed in Crystal XI. There seems to be a confusion here between SQL Expressions (which are NOT the main data source for a report) and Commands (which act as a data source for a report).

SQL Expressions are restricted to returning a single value from a single-row result set. Your problem was probably related to cases where your SQL Expression returned multiple rows. Using a MAX() or Min() can ensure you return a single value.

If you do need to return multiple values, there's at least one UFL that can do that via a Crystal formula that executes a SQL statement against any ODBC data source and returning values in multiple rows as a delimited list.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Ido,

I believe there is in fact a change in functionality of SQL expressions in XI. In 8.0, you could create subqueries that would return values per group, e.g., like:

(select sum(A.`amt`) from Orders A
where A.`customer ID` = Orders.`customer ID`)

This type of SQL expression cannot be created in XI, forcing the use of a command instead. The more usual SQL expressions work the same though.

-LB
 
Hi LB,

I wasn't aware of such a change. Was this stated anywhere in a user manual or a tech support document or did you simply reach this conclusion by direct experience?

Cheers,
- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Direct experience. Thread149-1047734 has a discussion with RosemaryL and others about some of this. The thread was about higher versions of CR in general, but my experience has just been with 8.0 and XI.

-LB
 
Interesting! I'll try to draw the attention of a Crystal Reports product manager to this and see if we can get a formal clarification.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
LB, here is the answer I received from one of the Crystal Reports product managers. If you can email me the sample reports demonstrating the problem, I'll forward them.
-----------------------------------
"I checked into this with the developers, and we couldn’t find a reason why it would work in 8.5, but not in 10 or XI. Just eyeballing the query, the developers couldn’t see anything special that would break, or what would cause it to be specifically Crystal breaking it. One possibility is that the DB driver changed if you’re using ODBC – we just pass SQL Expressions straight through to the DB driver. Is it possible that an earlier version of whichever driver you’re using supported the expression, but a newer version doesn’t? It would be helpful to verify if ODBCTest is able run the problem queries successfully.

Most importantly, do you have a report that shows the SQL Expression working in 8.5 and broken in Crystal Reports XI? It would be very helpful if you could send that to me."
------------------------------------------

Cheers,
- Ido



view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Ido,

I just sent you two reports (using the Xtreme database) with the same SQL expression, one created in 8.5 and one created in XI. Note that the one in 8.5 WILL work if the report is opened in XI, but will not work if created in XI.

-LB
 
Here is the update I just received from Business Objects on the status of this issue:
______________________________________

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.
____________________________________________

Cheers,
- Ido


view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
If someone is inspired to pursue the differences, use SQL Server, and do a trace of the query being passed.

You may find a means to get around these issues once you learn what is being received by the database.

-k
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top