Well once again - glad I couldn't help! David
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: Tuesday, March 01, 2005 11:25 AM Subject: [RBG7-L] - Re: Union / Outer Join David - NEVERMIND!!! I narrowed it down to a data issue in the activity table. After some further testing, I THINK the reason I was getting mixed results was because there were NULLS in the item number field in Activity. I'll skip the long and winded version. After I projected the activity table without the nulls everything works as I want it to. And it is MUCH quicker with the projected table!!! Thanks Dawn -----Original Message----- From: Dawn Oakes Sent: Tuesday, March 01, 2005 10:29 AM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Union / Outer Join David, >From the following select: select t1.partnum,t1.monthend,0 as Received fro VOCENDWEIGHTS t1 whe partnum not in (sel itemno fro ACTIVITY whe datereceived bet (addmon(t1.monthend, -1)) and t1.monthend) Would YOU expect to see all rows in vocendweights for each partnumber, monthend that have no receivings? I'll give you a run down of what I'm encountering (long and winded)- but first I need to know if the way I'm asking for the data is an issue. Thanks Dawn -----Original Message----- From: David M. Blocker [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 8:55 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Union / Outer Join Well, glad my input was interesting if nothing else. let me know what happens tomorrow and we'll see if it makes sense for me to jump in again to help! David 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 5:45 PM Subject: [RBG7-L] - Re: Union / Outer Join >> What do you think? I think you're just the man I need for this! Thanks for taking the time. I like the idea of inserting each select individually into a temp table - I hadn't thought of that. You have it reversed though - VOCEndweights has all partnumbers and monthends already, with 0 for endweights if we don't have any. So I don't need to build a cartesian product, I don't think. I'm trying to get the sum(received) from the activity table for each partnumber, monthend combination with 0 if there was none received for that particular month. As I was trying some of your suggestions, comparing results, I ran my original code again; the second select statement gave me what I wanted. So now I'm really confused. I thought maybe I changed something, so I copied the select statement from my earlier email and ran it - still worked, it gave me all month ends, not just the first one!?! I checked the database for errors, thinking maybe there is a problem with an index and there weren't any. I didn't do a reload, and if there was a problem, I'm sure it wouldn't fix itself. Here's my entire view definition: View: tvocact SELECT T1.Partnum as Partnum,T1.MonthEnd as MonthEnd,T1.EndWeights as EndWeights,T1.VOCType as 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 UNION SELECT T1.Partnum as PartNum,T1.monthend as MonthEnd,T1.endweights as EndWeights,T1.voctype as 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) Yields data like Partnum Monthend Endweights Voctype Received 7227 12/31/04 7.2 INK 2 7227 01/31/05 6.7 INK 0 7257 12/31/04 0 INK 0 7257 01/31/05 5 INK 5 Which is exactly what I want. It is still slow, so I will try inserting each select into a temp table individually. It bugs me that I don't know why it didn't work and now it does. Before, I was getting all the rows that had receivings and only the rows for 12/31/04 that didn't have receivings (none for 01/31/05). I know there are many ways to skin a cat, but should my view work as is or is there something wrong with it?? I'm going to sleep on it. I'll run it again tomorrow and see if I have trouble. I'll let you know. Thanks for your help - and for letting me go on and on....! Dawn
