uncleroydee
Technical User
Would anyone care to give me their opinion on the best way to write a select statement that encompasses the following:
Our database tracks part numbers, purchase contracts and RFQs (Request for Quote).
We buy some parts from multiple vendors to ensure supply, these parts carry the same number regardless of the vendor.
We test the part number table against an on hand quantity requirement table to ensure our needs are met.
We need to determine the following:
1. When a part number requirement is satisfied by a particular contract or RFQ
2. When a requirement is satisfied because the item is readily available through normal retail channels
3. When, even though the part is contained in the requirement table, the requirement is zero.
4. When a part number requirement which is not satisfied by one contract or RFQ is satisfied by another contract or RFQ
Items 1 through 3 are readily accomplished using a Case statement, but I am finding item 4 to be quite difficult. I could write a subquery that checks all part numbers, contracts and RFQs against the list generated in item 1 but the query runs for an excessively long time as it compares every number in the part number table (currently 14,000+) against every number in the quantity requirement table (currently 185,000!)
My question is this: Is there a way to write a Select statement or a Case statement that returns the instances of a part number that are satisfied by a contract or RFQ as "Satisified by this Contract or RFQ" and instances of the same part number that are not satisfied by their associated contract or RFQ as "Satisfied by another Contract or RFQ"? It appears that we need to avoid using a subquery that contains "NOT IN" or "NOT EXISTS" WHERE clauses, if possible, because of the processing demands placed on the server by these comparisons?
I'm certainly open to methods other the CASE statement, I've wondered about using some sort of cursor though I don't have any experience using cursors yet.
I welcome all advice and opinions.
Our database tracks part numbers, purchase contracts and RFQs (Request for Quote).
We buy some parts from multiple vendors to ensure supply, these parts carry the same number regardless of the vendor.
We test the part number table against an on hand quantity requirement table to ensure our needs are met.
We need to determine the following:
1. When a part number requirement is satisfied by a particular contract or RFQ
2. When a requirement is satisfied because the item is readily available through normal retail channels
3. When, even though the part is contained in the requirement table, the requirement is zero.
4. When a part number requirement which is not satisfied by one contract or RFQ is satisfied by another contract or RFQ
Items 1 through 3 are readily accomplished using a Case statement, but I am finding item 4 to be quite difficult. I could write a subquery that checks all part numbers, contracts and RFQs against the list generated in item 1 but the query runs for an excessively long time as it compares every number in the part number table (currently 14,000+) against every number in the quantity requirement table (currently 185,000!)
My question is this: Is there a way to write a Select statement or a Case statement that returns the instances of a part number that are satisfied by a contract or RFQ as "Satisified by this Contract or RFQ" and instances of the same part number that are not satisfied by their associated contract or RFQ as "Satisfied by another Contract or RFQ"? It appears that we need to avoid using a subquery that contains "NOT IN" or "NOT EXISTS" WHERE clauses, if possible, because of the processing demands placed on the server by these comparisons?
I'm certainly open to methods other the CASE statement, I've wondered about using some sort of cursor though I don't have any experience using cursors yet.
I welcome all advice and opinions.