SQL Problem When Upgrading to CR10 2

[Sacheveral]

I created some SQL expressions in reports when I was using Crystal 8.5, which all worked fine. They contained SELECT statements etc. When I upgraded to Crystal 10 last week some of them stopped working, and despite applying all the hot fixes they still do not work. The common element in the expressions that do not work is that they reference something in the main report's SQL. For instance the following works OK:

(SELECT COUNT(PN.Product_Needs_Id) FROM Product_Needs PN
WHERE PN.Centre_Name = 'Cambridge')

But this does not (where Centre.Centre_Name is intended to link to the Centre in question on the main report):

(SELECT COUNT(PN.Product_Needs_Id) FROM Product_Needs PN
WHERE PN.Centre_Name = Centre.Centre_Name)

As I say these work fine in Crystal 8.5. Any ideas?

Thanks,

Joe

 

[lbass]

I noticed that SQL expressions that I use in 8.0 did not work in CR XI. As a test, I took the SQL expression that did not work in the SQL expression expert and added it as part of the Select component of an "Add Command" query (which is where SQL expressions will end up) and it did work there. So you might try building your SQL statement from scratch, adding in the SQL expression syntax as a subquery in the Select portion of the command.

-LB

 

[rosemaryl]

Dear Sacheveral,

I noticed the same issue just today expect it works if I reference it the main select in the SQL Expression but not in another select within the same SQL Expression:

Example:

This works:

(Select min(GD."_GROUP_") from "_SMDBA_"."_GROUPDET_" GD
where
GD."_MEMBER_" =
"Work_Orders"."Seq.Open By")

This did not (and I can do it in CR 8.5 with no issues!)

(Select GP.Code from "_SMDBA_"."_GROUPS_" GP WHERE
GP.SEQUENCE =
(Select min(GD."_GROUP_") from "_SMDBA_"."_GROUPDET_" GD
where
GD."_MEMBER_" =
"Work_Orders"."Seq.Open By")
)

I get "The column prefix "Work_Orders" does not match with a Table Name or Alias used in the Query.

Very frustrating! I hate when they do things like and take away functionality in one version that existed in another and of course, no documentation of it.

Of course the only article on the issue reinforces Crystal'a position that Select statements in a SQL Expression have never been supported.

http://support.businessobjects.com/library/kbase/articles/c2016184.asp

Regards,

ro




Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

Ro,

Thanks for your input on this and for the BO reference. Using your example in CR XI, I would have been unable to do a SQL expression like:

(Select min(GD."_GROUP_") from "_SMDBA_"."_GROUPDET_" GD
where
GD."_MEMBER_" = "_SMDBA_"."_GROUPDET_"."_MEMBER_")

...although this would have worked in the SELECT part of a SQL statement written in the Add Command area. Can't remember the error message for the SQL expression, although I think it referred to an unspecified error, and now my trial period has run out.

-LB

 

[vidru]

As I recall, it started failing with CR 9. Something must have changed in the way they're parsing SQL Expression. I guess they figured that since giving us the Command option, we wouldn't have to resort to the sub-query/sql expression workaround.

-dave

 

[rosemaryl]

Dear Virdu,

Well they were wrong. I have lots of reports that were written at 8.5 that I had to resort to SQL expressions and now that the app I do consulting and report writing on has upgraded to 10 - I have problems.

I was writing a report for a customer when I discovered that little snafu in 10 - and rather then being able to present the CODE which the users will understand, I have to show the primary key (sequence) which the users don't know off the top of their head. A SQL command is not an option since the reports are served in an application which doesn't understand a SQL command as a datasource.

Sigh, now I have to modify part of my Advanced Crystal courseware that focuses on SQL expressions.

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[Sacheveral]

Thank you all for your comments. It sounds like BO are pulling the rug from under us. Being new to CR 10 I have no idea how to create a Command. Could anyone advise? Does it require Enterprise and/or repositories. We have the former (though I have no control over its setup) but not the latter. If they do require repositories, is there any alternative?

Many thanks,

Joe

 

[lbass]

Add Command" is one of the datasource options when you first start to develop your report. It allows you the option of writing your own SQL query, instead of having it generated by your record selection formulas, linking of tables, and selection of fields. You would essentially be creating from scratch what in 8.5 appears in "Show SQL query."

-LB

 

[Sacheveral]

I have successfully created this as an SQL command. Many thanks to all. However I still cannot cite a link to the main report's SQL in the WHERE clause of my SQL command. Instead I have to link the command to a table in the main report on the 'Links' tab of the database expert window. Whilst this works, it dramatically slows down the performance of the report as it appears to be doing the linking (and therefore filtering) of the SQL command at the client end, rather than on the server. There is certainly no mention of the link in the 'Show SQL Query' window.

If I am right, is there any way to trick it into linking between the command and the main tables at the server?

Many thanks,

Joe

 

[rosemaryl]

Dear Sacherval,

I think you misunderstood what you should do with the sql command.

Don't link to it.

Create a query that pulls the data the report was pulling (with the same field names) and add the subquery (that is the sql expression) into the query.

A cheat would be to go into your existing report's Database/Show SQL Query and copy the whole statement out of there ... then add the sql command (where you can now reference fields in the main query) and you are good to go.

That is what should go in the SQL Command.

Best regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762