Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Two Commands Post Reply Post New Topic
Author Message
delstar
Newbie
Newbie


Joined: 11 Jun 2009
Online Status: Offline
Posts: 26
Quote delstar Replybullet Topic: Two Commands
    Posted: 22 Oct 2009 at 12:04pm
I just tried using two Commands against the same database, and got really weird results. First, I should explain that the use of two separate commands was required since one table I was pulling data from needed to be UNIONed to a second table, but I needed data from a third table to display a comparison between it and the first two.

I put 2 crosstabs in : one showing a summary on the first two tables, the other showing a summary on the third. When the data started coming in, it grew quickly to over 10 million records read. I stopped it before it finished, but obviously something was wrong since there isn't anywhere near 10 million records between the three. If I take one of the crosstabs out, it appears to work fine. Any ideas on what would cause this behavior? If any additional info is needed, let me know.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Oct 2009 at 6:26am
I haven't done crosstabs, but if it works fine with one of the tables (which is what a command is), and there aren't that many records, it would seem that how you are joining your command tables together (or not) is producing a cartesian product.
 
I would look at the joins between the commands.
IP IP Logged
delstar
Newbie
Newbie


Joined: 11 Jun 2009
Online Status: Offline
Posts: 26
Quote delstar Replybullet Posted: 26 Oct 2009 at 5:42am
I pretty much stripped the commands to the bare basics. Started a new report, added 2 commands that pulled one field with one WHERE clause from different tables. Then added two crosstabs in to show the summary for each command. In fact, the run that produced over 10 mil. results was after I had done that. The original commands produced a lot fewer, but still a considerable amount above what it should have been. It should also be noted that the result count for both commands, according to summaries, were equal.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 26 Oct 2009 at 6:05am
I don't believe that the Command object was designed to retrieve data for a report.  I believe that ti was designed to retrieve data for parameter display.  Yes, I have seen many people use them for data for the report, but I think this is where they get into trouble.  If the SQL is so complex that you want to use a Command object, you should be using a stored proc, where you can control the results to a much greater extent.
 
HTH
IP IP Logged
delstar
Newbie
Newbie


Joined: 11 Jun 2009
Online Status: Offline
Posts: 26
Quote delstar Replybullet Posted: 26 Oct 2009 at 7:59am
Yes, a stored procedure would be optimal. Unfortunately, due to the sensitivity of the data (police database), modifying the database itself isn't acceptable.

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.