Report Design: How to modify a SQL query?

How to modify a SQL query?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5803
Printed Date: 03 May 2024 at 5:01pm

Topic: How to modify a SQL query?

Posted By: swillis
Subject: How to modify a SQL query?
Date Posted: 17 Mar 2009 at 10:18pm

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?

Replies:

Posted By: NoExpert
Date Posted: 05 May 2009 at 8:27am

Hi,

i have the same problem. in case you have found a solution please let me know.

thanks forward

Posted By: lockwelle
Date Posted: 06 May 2009 at 7:23am

What it sounds like is the location is wrong...Obviously.

How to change a location, if it is through an ODBC connection, create another connection that connects to the database you want. Then select the 'new' connection and click update, then go through every table and click update.

I haven't done this with 2 ODBC connections but I have done it with ODBC to OLE connection. If the client doesn't have the 'new' connection. You would need to update the 'original' ODBC connection and do the update all over again.

You might be able to get away with changing the ODBC connection and updating / verifying the database...sometimes a renew on the datasource is useful.

It's a pain, and seems overly not user friendly, but it is better than writing the report over again.

Hope some of this helps