December 21, 2010 at 6:53 am
In our ERP system we have a couple tables for the Purchase Orders - POHEADER and POLINES.
The status of the PO (overall) is stored in, POHEADER.STATUS, values= OP-Open, NW-New, CA-Cancelled, CL-Closed.
The status of each PO line is stored in POLINES.STATUS, same values as above.
We don't have to close PO Lines or the PO itself (STATUS = CL or CA) and we haven't been doing it. However, we want to create an "Open POs" report, the basics of the SELECT statement will be:
SELECT ... FROM POHEADER
LEFT OUTER JOIN POLINES ON ...
WHERE POHEADER.STATUS In ('OP','NW')
This requires us to clean up the database, there are over 21,000 rows selected (a lot of years of this).
I would like to clean up the bulk of the database manually. Here is my plan:
-- Close POLINES where recieved quantity >= ordered
UPDATE polines SET status = 'CL' WHERE QtyReceived >= QtyOrdered and status not in ('CL','CA')
-- Close POHEADERS where all POLINES are CL or CA
-- The 1st statement updates status to Cancel when every PO Line has been cancelled.
[Obviously I am making up the EVERY key word]
UPDATE poheaders SET status = 'CA'
LEFT OUTER JOIN polines ...
WHERE poheaders.status not in ('CA','CL') and EVERY polines.status = 'CA'
-- Now Close the remaining POs that qualify
UPDATE poheaders SET status = 'CL'
LEFT OUTER JOIN polines ...
WHERE poheaders.status not in ('CA','CL') and EVERY polines.status in ('CL','CA')
Is there a technique or functionality to achieve this?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
December 21, 2010 at 7:44 am
The following should give you what you want.
UPDATE poheaders
SET status = 'CA'
FROM poheaders h
LEFT OUTER JOIN polines l on h.poID = l.poID
AND NOT(l.status = 'CA')
WHERE h.status not in ('CA','CL')
AND l.poID IS NULL
AND h.poID IN (SELECT poID FROM polines)
UPDATE poheaders
SET status = 'CA'
FROM poheaders h
LEFT OUTER JOIN polines l on h.poID = l.poID
AND l.status NOT IN ('CA', 'CL')
WHERE h.status NOT IN ('CA','CL')
AND l.poID IS NULL
AND h.poID IN (SELECT poID FROM polines)
December 21, 2010 at 2:05 pm
Thanks!
I've been trying to test drive your solution - I see the technique you are using.
Been getting interrupted and haven't been able to focus on this long enough, also need to convert your statements to what is really needed (I posted a simplified version - there's another field in the POLINES, named, PARTCOMPFLG and has a value 'C' when someone goes in and checks 'Receive Complete' (i.e. consider this line item Complete, as is).
Anyway, I'm playing in our SANDBOXDB (copy of the live database). Using SELECT instead of UPDATE, to get the technique ingrained in my brain. Will be using various combinations of values to see the results to reinforce the technique.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply