Dawn <<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? >> It's a little hard to say because I don't know the structure and relationship between these tables. If LOOKS like there should be ONE row in VOCENDWEIGHTS for each Partnum / Monthend combination and potentially 0, 1 or MANY rows in Activity for each partnum with different DateReceived values. If this is correct, this also means that there are MANY rows in VOCENDWEIGHTS for each PARNUM - with different MonthEnd values. if this is true then your query is asking: Show mean ALL rows in VOCENDWEIGHTS - no matter what the montheend is - for a part number IF the part number is not in the list I get when I check the dateceived against the range of the month end and one month before. The result could be that you'll get a bunch of records in VOCENDWEIGHTS that you don't want. I don't think this is what you want. I think you want every part / monthend combination in VOCENDWEIGHTS if, FOR THAT Part number, there are no rows in ACTIVITY with a datereceived in the range defined by the monthend date minus one month. So would this be better? select t1.partnum,t1.monthend,0 as Received fro VOCENDWEIGHTS t1 whe partnum not in (sel itemno fro ACTIVITY T2 whe T2.ItemNo = T1.PartNum AND T2.datereceived bet (addmon(t1.monthend, -1)) and t1.monthend) 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 10:29 AM 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
