Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: One to Many Table Join Post Reply Post New Topic
Author Message
Geoda
Newbie
Newbie


Joined: 22 Apr 2014
Online Status: Offline
Posts: 3
Quote Geoda Replybullet Topic: One to Many Table Join
    Posted: 22 Apr 2014 at 7:57am
I've got 2 tables, ORDERS and STATUS. As the order status changes, an additional row gets added to the STATUS table so for every record in the ORDERS table there are many in the STATUS table. For example an order may have statuses of:
OPENED
CONFIRMED
PRINTED
CANCELLED

I'm trying to exclude a record from being read from the ORDERS table if the corresponding status includes CANCELLED in the STATUS table.I know that this can be done with a SQL subquery if using a command:

select * from ORDERS where ORDERNUM not in (
SELECT distinct ORDERNUM FROM ORDERS, STATUS
WHERE
ORDERS.ORDERNUM = STATUS.ORDERNUM
and STATUS.STATUS ='CANCELLED')

but wanted to avoid that as there are actually several more tables to be joined. I was also able to get the desired results by saving a list of cancelled orders in a formula and using that formula as a parameter in a sub report but that too is not very efficient.

Does anyone know of an easier way to accomplish this without a command or a subreport?
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 22 Apr 2014 at 8:35am
you could try using group select expert

group on ORDER_id
create a
@formula
if STATUS= "CANCELLED" then 1 else 0
in select expert>> group... place
sum(@formula,ORDER_id) = 0
IP IP Logged
Geoda
Newbie
Newbie


Joined: 22 Apr 2014
Online Status: Offline
Posts: 3
Quote Geoda Replybullet Posted: 22 Apr 2014 at 8:49am
Thanks for your response. I've been using that method and it works however the cancelled orders are suppressed, not eliminated, which means that I need to use running totals with an evaluation formula to do any summaries. It adds a level of complexity that's certainly doable but I was looking for a way to avoid all of that.
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 22 Apr 2014 at 9:32am
you could create sql command like

SELECT distinct ORDERNUM FROM ORDERS, STATUS
WHERE
ORDERS.ORDERNUM = STATUS.ORDERNUM
and STATUS.STATUS ='CANCELLED'

and join to your data to it  then in select expert
isnull(command.ORDERNUM )


Edited by kostya1122 - 22 Apr 2014 at 9:32am
IP IP Logged
Geoda
Newbie
Newbie


Joined: 22 Apr 2014
Online Status: Offline
Posts: 3
Quote Geoda Replybullet Posted: 23 Apr 2014 at 2:49am
That worked OK for a very small data set but the report performance is unacceptable when using it in a normal report. Any other suggestions?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 08 May 2014 at 11:08am
It is never a good idea to have multiple commands or a command linked to one or more tables in your report. Best practice is to have single command that pulls ALL of the data for the report if you're using a command.

The reason that you don't want to link a command with tables or other commands is that Crystal will not be able to push the joins down to the database for processing. Instead, it pulls ALL of the data in to memory and does the joins there. This is not as efficient as the database and will cause significant delays in report processing if you're dealing with tables or commands that bring back a lot of data before being filtered by the joins.

-Dell
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.063 seconds.