Dawn OK, you have two different problems, so let's look at them separately.
1. SPEED: If each SELECT is fast, we know that indexes / keys are not the issue. I'd try three things: 1. Try using not just UNION but UNION ALL in joining the SELECTs and see if that helps. 2. Try reversing the order of the SELECTs between the unions and see if that helps. 3. If neither helps, then just create a temporary table, and insert each SELECT into that table. Since the individual selects are fast, the inserts should be too. 2. SECOND SELECT NOT GIVING YOU WHAT YOU WANT: So it looks like what you want is a list of ALL partnumbers and EVERY month end value for each part number WHICH HAS NO DATA with a 0 next to it, right? In other words, if Part A has this data in VOCEndWeights: PartNum MonthEnd A 1/31/04 A 3/31/04 A 5/31/04 Then given an ending date for your data of 6/30/04, you'd want the SELECT to return: A 2/28/04 0 A 4/30/04 0 Right? If so, then notice you CAN'T ask for the other columns like VOCType and EndWeights because there ARE NOT RECRODS for that part / month end date, right? If all this correct, then your SELECT is NOT asking the righ question! Think of this way: You need a master list of all possible Month End dates / partnumbers, and then you want all the records from THAT list whose part number / month end date combination is not in the VOCEndWeights table. SO what if you did this: Project TEMP zEndDates as SELECT DISTINCT MonthEnd from VOCEndWeights (WHERE MonthEnd BETWEEN x and y) where x and y is the range of dates you want tested This is a list of distinct end dates. Now to create a master list of all possible combinations of partnumber and date, assuming Activity is your master list of item / part numbers): PROJECT TEMp zPartNumEndDates AS SELECT T1.ItemNum,T2.MonthEnd FROM Activity T1, MonthEnd T2 CREATE INDEX zItemNo on zPartnumEndDates (ItemNum) CREATE INDEX zMonthEnd ON zPartNumEndDates (MonthEnd) This "cartesian product" will give you one row for each month end and part number. The indexes will help with the select below Now, I think this select will give you want you want: SELECT t1.ItemNum, t1.monthend,NULL,NULL,0 fro zPartNumEndDates WHERE T1.MonthEnd NOT IN (sel T2.MonthEnd from VOCEndWeights T2 WHERE T2.Partnum = T1.ItemNum) What do you think? David Blocker David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Dawn Oakes" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Monday, February 28, 2005 12:04 PM Subject: [RBG7-L] - Union / Outer Join I have this select that I use to get the total qty received for part numbers grouped by each month end date: SELECT T1.Partnum,T1.MonthEnd,T1.EndWeights,T1.VOCType,(sum(T2.received)) as Received FROM VOCendweights T1, activity T2 WHERE T1.Partnum = T2.ITEMNO AND T2.datereceived BETWEEN (ADDMON(T1.MonthEnd ,-1)) and T1.MonthEnd GROUP BY T1.Partnum,T1.MonthEnd,T1.EndWeights,T1.VOCType Works fine. But I've been struggling with trying to do a union select to get the part numbers with no receivings for each monthend. I tried to UNION the select below, but 1) it's not returning the results I'm looking for and 2) When I do the Union, it's taking FOREVER! Though individually, the selects work quickly. I tried an outer join and that didn't work the way I wanted it to either. SELECT t1.partnum,t1.monthend,t1.endweights,t1.voctype,0 as Received fro vocendweights t1 whe partnum not in (sel itemno fro activity whe datereceived bet (addmon(t1.monthend, -1)) and t1.monthend) It's only referencing the first month end in the table. Which makes sense based on what I asked for, but how do I get it to give me a list of 0 receivings for EACH month end?? I'm trying to create a view so I can base a report off it. I can create a variable for each month that would sum the receivings for the report, but I thought this might be better (maybe not!). Any suggestions appreciated. Thanks Dawn
