CR 8.5 to CR 11- Modified SQL Query

[sgoodman7]

CR 8.5 report contains a modified SQL query. It will open and execute fine in CR 11. However, if this SQL ever needs to be modified, i.e. add a subquery in the WHERE clause, I don't see an easy way to do this. I have attempted the Add Command functionality, but this doesn't seem to allow you to modify the existing SQL query.

Any suggestions greatly appreciated.

 

[lbass]

If you add a subquery via a command, it will appear in "Show SQL query", although this doesn't allow you to modify the original query in other ways.

-LB

 

[synapsevampire]

Remove the existing data source and add in the equivalent using the Add Command. You can edit Add Command SQL.

How the original query was created and modified may alter this, if they just used Database->Show SQL Query to edit it in CR 8.5, then you're fine.

If they used the ADO connection and pasted in the SQL, you'll need to obtain the query used from going in there. A lesser known way to add in custom SQL was by using ADO/RDO in CR 8.5.

-k

 

[sgoodman7]

If I try to replace the existing data source, any report field referencing this data source is removed from the report. It doesn't allow me to map the old columns to the new columns.

 

[paulmarr]

Use Datasource Locations - this is where you can choose a new data source and map the fields to each other.


Cheers
paulmarr

 

[sgoodman7]

When I try to use the Add Command as the new Data Source Location (thru Set Datasource), it will allow me to input a SQL query, but it replaces each previously referenced table with that query. So if I have 3 tables, I now have 3 SQL queries. The option to map to the new Data Source is not presented.