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?
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.
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
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.
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