If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
Updating this question with a refresh:
http://www.clanimperium.net/1.JPG http://www.clanimperium.net/2.JPG http://www.clanimperium.net/3.JPG http://www.clanimperium.net/4.JPG (capital JPG is important) 1 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; (no WHERE clause at all - results as expected) 2 shows results of the subquery on it's own SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL; (results as expected) 3 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; WHERE pd.SERIAL_NUMBER NOT IN ('999789'); (manual NOT IN clause - results as expected - 999789 excluded) 4 Shows results of the full query SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL); (Results NOT as expected 999789 isn't excluded) TRIMming the subquery doesn't help. VAL can't be used because serial_number can be alphabetic. Essentially NOT IN doesn't work with a list generated from a subquery. Could someone attempt to reproduce these results? Any comment from Microsoft from official? Anyone with an alternative method? This whole project is frozen until this is fixed. |
#2
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
So using TRIM on both Serial_Number fields does not work. THe following fails
SELECT po.PURCHASE_DATE , pd.QTY , pd.SERIAL_NUMBER , pa.PART_ID , pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID WHERE Trim(pd.SERIAL_NUMBER) NOT IN (SELECT Trim(SERIAL_NUMBER) FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL); As a check have you tried something like the following to make sure the record in the INSTALL table actually contains the value 999789. SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER = '999789' An alternative query: SELECT po.PURCHASE_DATE , pd.QTY , pd.SERIAL_NUMBER , pa.PART_ID , pa.DESCRIPTION FROM ((PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID) LEFT JOIN INSTALL as I ON Pd.SERIAL_Number = I.Serial_Number WHERE I.SerialNumber Is Null John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Farkyss wrote: Updating this question with a refresh: http://www.clanimperium.net/1.JPG http://www.clanimperium.net/2.JPG http://www.clanimperium.net/3.JPG http://www.clanimperium.net/4.JPG (capital JPG is important) 1 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; (no WHERE clause at all - results as expected) 2 shows results of the subquery on it's own SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL; (results as expected) 3 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; WHERE pd.SERIAL_NUMBER NOT IN ('999789'); (manual NOT IN clause - results as expected - 999789 excluded) 4 Shows results of the full query SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL); (Results NOT as expected 999789 isn't excluded) TRIMming the subquery doesn't help. VAL can't be used because serial_number can be alphabetic. Essentially NOT IN doesn't work with a list generated from a subquery. Could someone attempt to reproduce these results? Any comment from Microsoft from official? Anyone with an alternative method? This whole project is frozen until this is fixed. |
#3
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
Farkyss wrote:
Updating this question with a refresh: http://www.clanimperium.net/1.JPG http://www.clanimperium.net/2.JPG http://www.clanimperium.net/3.JPG http://www.clanimperium.net/4.JPG (capital JPG is important) 1 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; (no WHERE clause at all - results as expected) 2 shows results of the subquery on it's own SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL; (results as expected) 3 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; WHERE pd.SERIAL_NUMBER NOT IN ('999789'); (manual NOT IN clause - results as expected - 999789 excluded) 4 Shows results of the full query SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL); (Results NOT as expected 999789 isn't excluded) TRIMming the subquery doesn't help. VAL can't be used because serial_number can be alphabetic. Essentially NOT IN doesn't work with a list generated from a subquery. Could someone attempt to reproduce these results? Any comment from Microsoft from official? Anyone with an alternative method? This whole project is frozen until this is fixed. Perhaps you could tell us what you're trying to do in english before going directly to code. Also, some sample data or data base along with any relationships would be helpful. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#4
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";
produces expected results whereas SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789"; procudes an unexpected, empty result. FYI, the lookup for this field in the INSTALL is SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER]; have also tried replacing that with this SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER]; as the original lookup was made by access's 'insert a field from another table' wizard This produces the same incorrect results "John Spencer" wrote: So using TRIM on both Serial_Number fields does not work. THe following fails SELECT po.PURCHASE_DATE , pd.QTY , pd.SERIAL_NUMBER , pa.PART_ID , pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID WHERE Trim(pd.SERIAL_NUMBER) NOT IN (SELECT Trim(SERIAL_NUMBER) FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL); As a check have you tried something like the following to make sure the record in the INSTALL table actually contains the value 999789. SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER = '999789' An alternative query: SELECT po.PURCHASE_DATE , pd.QTY , pd.SERIAL_NUMBER , pa.PART_ID , pa.DESCRIPTION FROM ((PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID) LEFT JOIN INSTALL as I ON Pd.SERIAL_Number = I.Serial_Number WHERE I.SerialNumber Is Null John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Farkyss wrote: Updating this question with a refresh: http://www.clanimperium.net/1.JPG http://www.clanimperium.net/2.JPG http://www.clanimperium.net/3.JPG http://www.clanimperium.net/4.JPG (capital JPG is important) 1 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; (no WHERE clause at all - results as expected) 2 shows results of the subquery on it's own SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL; (results as expected) 3 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; WHERE pd.SERIAL_NUMBER NOT IN ('999789'); (manual NOT IN clause - results as expected - 999789 excluded) 4 Shows results of the full query SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL); (Results NOT as expected 999789 isn't excluded) TRIMming the subquery doesn't help. VAL can't be used because serial_number can be alphabetic. Essentially NOT IN doesn't work with a list generated from a subquery. Could someone attempt to reproduce these results? Any comment from Microsoft from official? Anyone with an alternative method? This whole project is frozen until this is fixed. . |
#5
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
Farkyss wrote:
SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789"; produces expected results whereas SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789"; procudes an unexpected, empty result. FYI, the lookup for this field in the INSTALL is SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER]; have also tried replacing that with this SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER]; as the original lookup was made by access's 'insert a field from another table' wizard This produces the same incorrect results So using TRIM on both Serial_Number fields does not work. THe following fails [quoted text clipped - 77 lines] project is frozen until this is fixed. . Farkyss, I was having problems with some of the queries then I realized I had a typing error. I was using 999789 in Install and 999879 in PORD. I'm using Acc 2003 on XP SP3. Once I adjusted that an now am getting expected results using John Spencer's query (orig) Here are images from my tests: My test files http://www.box.net/files#/files/0/f/.../1/f_397107264 John's Query SQL http://www.box.net/files#/files/0/f/.../1/f_397107274 Result of John's query http://www.box.net/files#/files/0/f/.../1/f_397107256 Also, I am able to run successfully SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789"; and SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789"; -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
The PORD table contains serial number/quantity lines in a PURCHASE ORDER
table (as each purchase order can be of several different items, each in need of their own serial number) The INSTALL table contains records of certificates sent as proof that a part has been fitted by an engineer. The important part of that record is the serial number. When entering details of an install certificate, I limited the choice of serial numbers to those that actually existed - this would reduce errors later on, The code listed below is a shorten version i need for a QUERY to pull out details from Purchase Order/PORD/PART tables for items whose serial numbers DO NOT appear in the INSTALL tables. This information basically tells us what items have been sold that don't have installation certificates for The links to JPG give test data and screenshots of the results "orange via AccessMonster.com" wrote: Farkyss wrote: Updating this question with a refresh: http://www.clanimperium.net/1.JPG http://www.clanimperium.net/2.JPG http://www.clanimperium.net/3.JPG http://www.clanimperium.net/4.JPG (capital JPG is important) 1 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; (no WHERE clause at all - results as expected) 2 shows results of the subquery on it's own SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL; (results as expected) 3 shows results of SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID; WHERE pd.SERIAL_NUMBER NOT IN ('999789'); (manual NOT IN clause - results as expected - 999789 excluded) 4 Shows results of the full query SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL); (Results NOT as expected 999789 isn't excluded) TRIMming the subquery doesn't help. VAL can't be used because serial_number can be alphabetic. Essentially NOT IN doesn't work with a list generated from a subquery. Could someone attempt to reproduce these results? Any comment from Microsoft from official? Anyone with an alternative method? This whole project is frozen until this is fixed. Perhaps you could tell us what you're trying to do in english before going directly to code. Also, some sample data or data base along with any relationships would be helpful. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 . |
#7
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
Did your values in the INSTALL table come from PORD via a lookup table?
That's the issue as I see it. "orange via AccessMonster.com" wrote: Farkyss wrote: SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789"; produces expected results whereas SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789"; procudes an unexpected, empty result. FYI, the lookup for this field in the INSTALL is SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER]; have also tried replacing that with this SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER]; as the original lookup was made by access's 'insert a field from another table' wizard This produces the same incorrect results So using TRIM on both Serial_Number fields does not work. THe following fails [quoted text clipped - 77 lines] project is frozen until this is fixed. . Farkyss, I was having problems with some of the queries then I realized I had a typing error. I was using 999789 in Install and 999879 in PORD. I'm using Acc 2003 on XP SP3. Once I adjusted that an now am getting expected results using John Spencer's query (orig) Here are images from my tests: My test files http://www.box.net/files#/files/0/f/.../1/f_397107264 John's Query SQL http://www.box.net/files#/files/0/f/.../1/f_397107274 Result of John's query http://www.box.net/files#/files/0/f/.../1/f_397107256 Also, I am able to run successfully SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789"; and SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789"; -- Message posted via http://www.accessmonster.com . |
#8
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
This alternative query doesn't work either. Believe it's because Access isn't
processing the values of SERIAL_NUMBER in the INSTALL table correctly since they're looked-up An alternative query: SELECT po.PURCHASE_DATE , pd.QTY , pd.SERIAL_NUMBER , pa.PART_ID , pa.DESCRIPTION FROM ((PURCHASEORDER AS po INNER JOIN PORD AS pd ON po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID) LEFT JOIN INSTALL as I ON Pd.SERIAL_Number = I.Serial_Number WHERE I.SerialNumber Is Null |
#9
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
Farkyss wrote:
Did your values in the INSTALL table come from PORD via a lookup table? That's the issue as I see it. SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789"; [quoted text clipped - 39 lines] and SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789"; No, I simply typed them in. That's part of why I suggested some table structures or a sample database to help with solution. -- Message posted via http://www.accessmonster.com |
#10
|
|||
|
|||
SQL "NOT IN" doesn't function as expected (refresh)
You can download the db with some junk data in from he
http://www.clanimperium.net/testdb.accdb "orange via AccessMonster.com" wrote: Farkyss wrote: Did your values in the INSTALL table come from PORD via a lookup table? That's the issue as I see it. SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789"; [quoted text clipped - 39 lines] and SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789"; No, I simply typed them in. That's part of why I suggested some table structures or a sample database to help with solution. -- Message posted via http://www.accessmonster.com . |
|
Thread Tools | |
Display Modes | |
|
|