Upgraded from CR8.5 to CR10, Manually edit SQL?

[BlindPete]

We have a proprietary dbase that we must interact with and this often requires manually adjusting the syntax the SQL to make it work. I can not seem to figure out how to do that in CR10? I can view the SQL but not modify it.

Anyone know how to manually adjust the SQL statement in CR10?

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/

 

[vidru]

I don't think you can edit SQL from the Show SQL Query window post CR 8.5. If you want to edit the SQL for a report, I believe that the report would need to be created using a SQL Command as the data source (as opposed to tables or views directly). In your case, you could copy the text of the Show SQL Query window, and paste the CR generated SQL into a Command (or write your own).

Here's a snippet from a document on the Business Objects site:

Show SQL Query in Crystal Reports 8.5
In Crystal Reports 9, the Show SQL Query command displays the SQL
statements generated by the database drivers. Do not edit the SQL statement in
the Show SQL Query command. Instead, use full SQL functionality to write
and edit simple and complex SQL statements by using the Add Command node
in the Database Expert.

-dave

 

[BlindPete]

Yes I cna use the command option, but it does not help becuase if you use that option all your fields attract the table name "command". In other words the whole report needs to be replaced. Thanks though!

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/

 

[GJParker]

There is no way around this, the Add command is the only option you have to use custom SQL.



Gary Parker
MIS Data Analyst
Manchester, England

 

[vidru]

You might be able to get the report to re-map the fields if you use Set Datasource Location, though I can't remember if you can create the Command from there.

-dave

 

[synapsevampire]

Try to avoid proprietary approaches to data sources in general.

Even the new Business Views in Crystal seem like a trap to me.

The best approach would be to use Views or SP's, that way they can be used if a report requirement changes (or any process) or the database changes by simply modifying the View/SP.

-k

 

[BlindPete]

Yes I concur, but I'm trapped by a legacy I can not alter.

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/