cancel
Showing results for 
Search instead for 
Did you mean: 

Database - Show SQL Query question..

Former Member
0 Kudos

Greetings:

I am helping a co-worker with Crystal. I don't use crystal but I am able to explain the problem. My co worker is currently using crystal reports 8.5. If he pulls a query and the report is generated he can then click on Database menu then click on Show SQL Query. From there he is able to edit, add, remove fields. Now we want to place him on Crystal V10. When he does the same task, instead of being able to edit the Show SQL query he can only view it. Is there a way around this so that he can Edit the sql query in version 10 or did that feature go away in versions 9 and above. Also we are going to Purchase Crystal 2008, is this feature available in 08? Any help would be appreicated..

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Keith,

I never used 8.5, so I never knew that you could ever do that. In what version it went away, I don't know... It's just gone... sort of.

It just got replaced with SQL Commands.

Copy the SQL code

Go back to the Database Expert and expand your current connection

Select "Add Command"

Paste your edited SQL code into the Command window.

Hint: don't SQL Commands with tables or other SQL Commands.

Hope this helps,

Jason

Former Member
0 Kudos

Hi there!

I have the same problem as described above. I have been asked to figure out what is wrong with one of the reports that used to work when we used MAS 4.05. We are now on MAS 4.20 with Crystal Reports v.10 and the report fails to run. I loaded the report into Crystal to see if I could see the problem and I discovered that the report is still trying to access a table from the old MAS database. The SQL code as shown in the Show SQL Query window is as follows:

SELECT "AR_InvoiceHistoryHeader"."InvoiceNo", "AR_InvoiceHistoryDetail"."ItemCodeDesc", "AR_InvoiceHistoryDetail"."ItemCode", "AR_InvoiceHistoryDetail"."ExtensionAmt"

FROM "AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader", "AR_InvoiceHistoryDetail" "AR_InvoiceHistoryDetail"

WHERE "AR_InvoiceHistoryHeader"."InvoiceNo"="AR_InvoiceHistoryDetail"."InvoiceNo" AND "AR_InvoiceHistoryHeader"."HeaderSeqNo"="AR_InvoiceHistoryDetail"."HeaderSeqNo" AND "AR_InvoiceHistoryHeader"."CommissionAmt"="AR_InvoiceHistoryDetail"."CommissionAmt" AND ARO_InvHistoryDetail."SOItemNumber" <> 'COMMENT' AND ARN_InvHistoryHeader."InvoiceDate" = {d '2007-09-21'}

ORDER BY "AR_InvoiceHistoryHeader"."InvoiceNo"

The problem is that ARN_InvHistoryHeader needs to be AR_InvoiceHistoryHeader and SOItemNumber needs to be ItemCode.

When I go into the Database Expert, I can only see the tables in the FROM clause. I don't know how to modify the WHERE clause. It kind of looks like this report was only partially converted to the new version. Is there a way to changed these attributes without rebuilding the whole report?

By the way, I apologize for not setting my SQL off in a code box, but I tried that and it caused the page to be very wide with no horizontal scroll bar.

Edited by: Jason Schill on Oct 27, 2008 11:30 PM

Former Member
0 Kudos

Jason,

If you have changed back end databases, you will have to "move" your reports to to the data source. The easiest way to do this is to to use the "Set Data Source Location" feature located under the Database menu.

If your table name and / or field names have changed the old report fields will have to be mapped to the new database.

The Help files should provide more documentation on the specifics.

Quick Tip... If this is a production report... always do this with a copy of the report that way you are protected from anything tragic.

Thanks,

Jason

Former Member
0 Kudos

Thanks, Jason! That is just what I did. I still find it interesting that they don't allow you to simply modify the SQL to match exactly what you are trying to query. I guess I'm just not accustomed to tools like these. I'm accustomed to working with databases more directly.

Answers (0)