Using 'NOT IN' Within a SQL Statement
540109Jan 12 2007 — edited Jan 16 2007Does anyone know why the following SQL statement does not return any values? The problem is with the SELECT statement within the 'NOT IN' clause. When explicitly typing in the part_ids returned from the select statement, everything works as expected.
Thanks for any help.
SELECT Distinct Part_Id, Trans_Date
FROM Invctrl
WHERE Trans_Type = 0
AND Trans_Date BETWEEN To_Date( '1-Mar-2006 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )
AND To_Date( '31-Jan-2007 23:59:59', 'dd-mon-yyyy hh24:mi:ss' )
AND Part_Id NOT IN (SELECT part_Id From InvCtrl WHERE Trans_Date < To_Date( '1-Mar-2006 00:00:00', 'dd-mon-yyyy hh24:mi:ss'))