Crystal not able to form proper SQL

[Wholsea]

I have a report that was developed and runs fine in Crystal 10.

When the report is opened in XI developer, or tried to run from InfoView it appears as if the linking syntax is not rendered properly...

For example, SQL rendered in Crystal 10 looks like this:

select field_1, field_2
from table_1, table_2, table_3
where table_1.field = table_2.field
and table_1.field = table_3.field
and table_3.field_2 is null
and table_3.field_3 = "some value"

This same report in Crystal XI will render this SQL:


select field_1, field_2
from table_1, table_2, table_3
where and table_3.field_2 is null
and table_3.field_3 = "some value"

Note that the table linking is gone, and the statement is mal-formed starting with an and in the where clause.

I can't for the life of me figure out how to correct this without having to re-write the entire report. The actual report is rather complex with multiple sub-reports and constraints on sections and grouping.

Any advice on how to get Crystal XI to correctly write the SQL syntax so that we are able to render valid instance of this report?

TIA

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[synapsevampire]

Please post your database and connectivity used.

Did you do a Database->Verify Database?

If so, I'd suspect a no longer supported method of connectivity.

And perhaps you could post actual examples rather than pseudo code.

-k

 

[Wholsea]

Using naitive Oracle connectivity, it connects to the database file, its query was just goobered.

I was able to get Crystal to re-write the query by going to Database, Show SQL Query, then clicking on Reset.

This re-wrote the Crystal 10 query to the ANSI-92 standard SQL that Crystal uses...

This caused another problem. For whatever reason, some of the constraints on this report have now been removed and we are getting abundant amounts of extra data. (Constraints that were trimmed in the 10 version)

Has anyone else had conversion problems between 10 and XI?

Most of our reports were converted w/ no problems, this one seems to be the exception...


ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Turkbear]

Hi,
What are you referring to as Constraints?
Record and Group selection formulas are not Constraints ( in the database sense, anyway)...

Not sure what 'trimmed' means in that context...

BTW ( as a side note for Oracle folks), when updating V10 reports and resaving as XI versions, XI will change the SQL code from the (+) syntax to the ANSI version and this can, in some cases, result in much slower performance..( The parsing seems much more index-dependent and this can cause, on complex joins and views, more full table scans)




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

You may find that the record selection changes, but you should be able to get it all to pass again by modifying it.

Post specifics about the changes.

-k

 

[Wholsea]

As stated before the report has many pieces to it (including 4 sub-reports)

I finally got tired of fiddling with it and re-wrote it as a command...

Sucks to have to do that, but it took less time than it would have taken to try to "fix" it...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[cmmrfrds]

turkbear, we recently converted about 1500 reports from Crystal 10 to Crystal XI going against Oracle 9.2. We did not detect any that ran slower due to the new ANSI syntax on the Joins. The only thing we ran into (a few cases) was too many columns on the joined tables ora-01445, but this was a fairly simple fix.

 

[gortuk]

When I upgraded from CE 9 to BOXIR2 SP1, we had one particular Oracle-based report which started taking hours to run instead of seconds. Exactly the same report file, when opened in CR 9, worked fine.

It turned out that when the report was opened in Crystal Reports XI or run through Business Objects XI, three of the joins were removed from the SQL completely so the query was extremely inefficient.

I don't know if this would help in your case because your SQL looks even more messed up. But our quick fix was to add the missing joins to the Select Expert so that they would be added to the SQL.

{TABLEA.CHILDID} = {TABLEB.ID} and
{TABLEC.ID} = {TABLEA.ID} and
{TABLEB} = {TABLED.USERID}

I was never able to get an answer from Business Objects support as to why XI broke the SQL so badly.

 

[synapsevampire]

Wholsea: I've heard the nightmares along those lines, I would have tried breaking all of the links and then repointing all of the table using Set Database Location and then joining them again, one person told me this worked for them...

-k

 

[Wholsea]

Synap: We tried that, I went so far as to remove all of the tables, re-add them and re-link them with no luck.

We haven't had any issues with any other reports, it seemed to only be with this one.

Gortuk: We tried that as well, to no avail...

The only way we could figure how to fix it was to re-write the report as a command... Which is what it should have been written in to begin with. *sigh*

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise