Print Page | Close Window

SQL Query 8.5 to 11 Problem

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6076
Printed Date: 07 May 2024 at 12:03pm


Topic: SQL Query 8.5 to 11 Problem
Posted By: igendreau
Subject: SQL Query 8.5 to 11 Problem
Date Posted: 14 Apr 2009 at 1:07pm
I'm running a report that our parent company sends us to run a report from an Oracle database. It runs fine in Crystal 8.5, but gives a "Failed to retrieve data from the database" error in versions 10 or 11. A tech there told me it's because of the "WHERE" clause. She said you used to be able to manually type it in earlier versions but can't in v11? Here is the WHERE statement:

WHERE ABS(HM_BACKLOG_DETAILS.QTY_ORDERED) > NVL(ABS(HM_OE_LINE_INVOICE.QTY_INVOICED),0)

When I click on Database > Show SQL Query, and hit Reset, this WHERE statement is the only thing that disappears. So I just need to rework this so it works. It's a backlog report. Basically this just says "if quantity ordered is greater than the quantity invoiced, include it in the report".

At first I thought I'd just setup two formulas: qtyOrdered, and qtyInvoiced and figure out the ABS and NULL Issues there. Then I'd just go into the Select Expert and say I want records where qtyOrdered > qtyInvoiced. But I'm not sure how to do that. The Select Expert lets me do the "qtyOrdered is greater than" part, but doesn't want to let me put the other formula in the box on the right.

Any ideas would be greatly appreciated. If you need more info, let me know, glad to provide it. Thanks!!


Replies:
Posted By: hilfy
Date Posted: 14 Apr 2009 at 1:52pm
That's exactly what the problem is - in Crystal 8.5 and earlier you could edit the SQL that Crystal generated to update the where clause.  But later versions of Crystal won't read that updated SQL correctly.  Instead you can enter the whole SQL as a Command, set variables on it, etc.
 
However, for your purposes, you're headed in the right direction.  In the Select Expert, click on the Formula button.  In the formula enter:
 
mailto:%7b@qtyOrdered - {@qtyOrdered } > mailto:%7b@qtyInvoiced - {@qtyInvoiced }
 
If you already have other selection critera, you'll need to put "and" before this and add it to the end.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: igendreau
Date Posted: 15 Apr 2009 at 6:36am
Okay, good news/bad news...
 
Good news is:  I have a report that works. 
 
Bad news is: It now takes 45 minutes to run instead of under 5.  I'm assuming that's because my WHERE statement is now in the Select Expert and not in the SQL Query (When I go into Show SQL Query, there is no WHERE statement).  I am guessing that this is why it was written into the SQL Query in the first place, to speed up the report's performance.
 
Question is, is it possible to get it in the SQL Query anymore, or is that what has gone by way of the dinosaur in higher versions?
 
Any other thoughts on how I can speed up the performance?
 
Thanks for the help!

Posted By: hilfy
Date Posted: 15 Apr 2009 at 6:52am
Because you're filtering based on formulas inside Crystal, Crystal is pulling in all of the records and doing the filtering itself.  That's why it takes so long.  You have a couple of options to alleviate this:
 
1.  You could try creating your formulas a SQL Expressions instead of as formulas and use those in your filter. (NOTE: I haven't used SQL Expressions this way so I don't know whether Crystal will let you do this or not...)
 
2.  Instead of using separate formulas for qtyOrdered and qtyInvoiced, you could put the calculations in the Selection Formula directly.
 
3.  You may be able to convert the whole thing to run on a Command, which would allow you to include the correct where statement from the start and which would be guaranteed to run on the database.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: igendreau
Date Posted: 15 Apr 2009 at 8:29am
Okay, that makes sense that it's taken so long.
Unfortunately, I'm a little shaky on the 3 options and exactly what to do.  We're pushing my current knowledge of Crystal just a bit, but glad to do some trial and error.  Is one of the options more of what you'd recommend?

Posted By: hilfy
Date Posted: 15 Apr 2009 at 9:06am
Try option 1 first.
 
1.  In the Field Explorer, right-click on SQL Expression Fields and select New.  Name it qtyOrdered and put "ABS(HM_BACKLOG_DETAILS.QTY_ORDERED) " in the formula.  Do the same with the calculation for qtyInvoiced.  NOTE:  SQL Expressions are just that - they have the syntax used in your database, NOT Crystal syntax.
 
2.  Go to the Select Expert and Show Formula.  Replace the part that you entered before with "{%qtyOrdered} > {%qtyInvoiced}".
 
Now run your report.  This should send the filter to the database.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: igendreau
Date Posted: 15 Apr 2009 at 10:30am
Thank you!!  It worked like a charm.  Now runs in about 90 seconds.  You're a lifesaver.  Thanks again!!


Print Page | Close Window