How to modify a SQL query?

[Former Member]

Hi all,

I am using Crystal Reports version 10. I have a number of reports that have been written by a software vendor wherby the name of the database they were connected to when the report was written is coded into the FROM command of the reports SQL query, eg "GCUK_2" in the of the SQL snippet below.

SELECT "Clients"."NAME", "Quotes"."QUOTE_ID", "Quote_Items"."UPRICE", "Quote_Items"."QTY", "Quote_Items"."UOM", "Quote_Items"."QUSAGE_ID", "Report_Control"."QUSAGEID", "Quote_Items"."STANDARD", "Quote_Items"."SECT_FLAG", "Quote_Items"."DISPORDER", "Quotes"."DESCRIPT", "Report_Control"."SECT_NAME", "Quote_Items"."CNT", "Category_and_Type"."TYPEDESC", "Quote_Items"."DESCRIPT", "Report_Control"."DISP_SORT"

FROM ((("GCUK_2"."schedwin"."QTE_CTRL" "Report_Control" INNER JOIN "GCUK_2"."schedwin"."QUSAGE" "Quote_Items" ON "Report_Control"."QUSAGEID"="Quote_Items"."QUSAGE_ID") LEFT OUTER JOIN "GCUK_2"."schedwin"."QUOTES" "Quotes" ON

I have tried setting the Datasource Location, but it doesn't change the query at all. I have read on another forum that you can generate another SQL query using the Database Expert, Current Connections, then right click the Add Command for the database you want to create a SQL command. Is this the only way to update the database names in the query?

Thanks,

Scott.

[former_member11696]

Please re-post if this is still an issue to the Data Connectivity - Crystal Reports Forum or purchase a case and have a dedicated support engineer work with you directly

[Former Member]

Hi Scott,

How did you gnerate the report? using tables or command? if you are using tables then the crystal will generate the query once you run the report and go to database-->show SQL. So if the report is generated using command then you can edit the command and change the query. But if it generated by the Crystal you should be able to do it by updating the connection using set datasource location.

Regards,

Raghavendra

[Former Member]

Hi Raghavendra,

The report was generated using tables. When I select Database, Set Datasource Location I would have expected that the SQL query would be updated to reflect the new database name, but this is not the case. The only way I can see to change the database name in the query is to change to the desired databsource and then remove and re-insert the tables, which will then update the query with the correct name. However, this seems to be a convoluted way of changing the db name in the query.

Scott

[Former Member]

Hi

Using a command object is a recommended way to do it.

You can also apply record selection formula in the report for your requirement, that would also change the SQL query.

Regards

Sourashree

[Former Member]

Hi Sourashree,

Thanks for that. All the reports were created by the vendor using tables as opposed to the command object. I would have thought that changing the datasource would automatically cause Crystal to rewrite the SQL query syntax, but this doesn't appear to be the case.

Yes, I've noticed that modifying the record selection will change the sql query. The only way I can see to change the database name in the query is to change to the desired databsource and then remove and re-insert the tables, which will then update the query with the correct name. However, this seems to be a convoluted way of changing the db name in the query.