Skip to Content
0

Crystal Reports SQL - Nested Queries - Failed to retrieve data from the database

Feb 08, 2017 at 09:03 PM

91

avatar image

Hey, I have a problem with SQL Expression Fields in CR. It involves using nested queries.

a bit of background: I have 2 tables (TableA & TableB), both have string columns of the same size (StringCol) and I'm trying to write a query to get SUM of another column (ValueCol) in TableA, when StringCol's match in both tables. Here's what I wrote:

(SELECT SUM(ValueCol) FROM TableA WHERE StringCol = (SELECT TOP(1) StringCol FROM TableB))

The inner query works and returns a single string value. The outer query works fine if I type the value (in single quotes) instead of the inner query. When I validate the expression I get "No Errors Found". But when I use it in my report gives me two error messages:

1- Failed to retrieve data from the database

2- Invalid argument provided

Can somebody shed light on this? What am I missing here? Thanks.

PS: CR Version 14.1.4.1327, External DB: Schneider ClearSCADA ODBC

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Mahdi Ale Mohammad Feb 09, 2017 at 04:58 PM
0

Hey all, to answer where :SQL Expression" section is: if you enable Fields Explorer in View menu, you'll find it right after Database and Formula fields in the tree. You may create queries of CR tables, parameters, formula, etc.

Abhilash has a point, table aliasing is required here. Here's a workaround I found. Although the original query should work in theory, I found rephrasing it to the following is the way to go:

(SELECT SUM(ValueCol) FROM TableA AS A, TableB AS B WHERE (A.StringCol = B.StringCol))

So I guess conclusion is avoid nested SELECTs and try to use aliasing instead.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Mahdi,

I know where SQL Expressions are created from and have used them on many of my reports!

The reason I asked where you've placed the SQL Expression field is to know whether it has been created to return a single value for the entire report or whether it has been created to return a value for every record/group.

I'm glad table aliasing helped.

-Abhilash

0
Taseeb Saeed Feb 09, 2017 at 11:30 AM
1

Hello,

You need to write this query as command or write a Store procedure and give it to CR.

Thanks

Engr. Taseeb Saeed

Show 1 Share
10 |10000 characters needed characters left characters exceeded

For more information about using commands, see my blog post here: Best Practices for Using Commands with Crystal Reports.

-Dell

1
Abhilash Kumar
Feb 09, 2017 at 08:07 AM
0

Hi Mahdi,

What section is this SQL Expression in?

You'd also need to use table aliases in this SQL.

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded