April 7, 2011 at 8:02 am
No a new problem I'm sure, but I curious as to whether there is a more effecient manner in which to solve my problem than I already have.
Objective: I have several orders, each of which have related Pack unload . Pack Unload TX's are held in seperate tables and there could be several thousand TX's
What I want to do is find all packs relating to a load
i.e 1 load, 22 unload packs.
I have two initial thoughts:
Option 1. Pull this information in as part of a T-SQL query source i.e. select load_id from loads left outer join pack_unloads ....... etc
(maybe not so bad, but it would return hell and all records)
Option 2. Have two sources. Loads & Pack unloads. Use a merge join to find the related packs Unload tx's for the record. (this has the downside that it would load the entire table - which is overkill)
My inclination is to lean towards option 1.........
Is there a more effecient way to do this. (previously I done select to get the list of jobs into a sql staging table and then used a select packs_unloaded where load_id in the staging table)
I'm trying if possible to avoid using staging tables if possible.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 7, 2011 at 12:00 pm
It sounds like CTEs might help you avoid staging tables and allow you to get results with one pass over the data...but it's hard to tell. I am sure it's second nature to you...but I am struggling with you're terminology for your entities. Can you please post some DDL for tables, DML for test data and queries you've tried that deliver your desired resultset?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply