Home » RDBMS Server » Server Administration » "not in" doesn't work with and condition
"not in" doesn't work with and condition [message #371096] Fri, 25 August 2000 06:44 Go to next message
Ankur
Messages: 22
Registered: August 2000
Junior Member
The foll query works with "not in"

select productionhistory.itemno
From productionhistory, productionplan, assemblyline, Items
Where productionhistory.PID = productionplan.PID
and productionplan.lineid = assemblyline.lineid
and productionhistory.Itemid = items.itemid
and productionhistory.subassemblystatus='TESTEDOK'
and assemblyline.lineid='13'
and productionhistory.phid not in
( select qcdetails.phid from qcdetails)
order by to_number(productionhistory.itemno)

But when one more "and condition" is added it doesn't display me the correct results

select productionhistory.itemno
From productionhistory, productionplan, assemblyline, Items
Where productionhistory.PID = productionplan.PID
and productionplan.lineid = assemblyline.lineid
and productionhistory.Itemid = items.itemid
and productionhistory.subassemblystatus='TESTEDOK'
and assemblyline.lineid='13'
and items.itemcode = '1'
and productionhistory.phid not in
( select qcdetails.phid from qcdetails)
order by to_number(productionhistory.itemno)

The above query works perfectly in access & dispalys correct results

can any one help me ?

Thanks
AnkuR.
Re: "not in" doesn't work with and condition [message #371103 is a reply to message #371096] Fri, 25 August 2000 15:23 Go to previous messageGo to next message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
It may be worth checking that you don't have leading or trailing spaces on your data. Has it been imported into Oracle from Access somehow?

Try something like this with your varchar2 fields:
select myfield||'<<<' from...
or
where length(myfield) != length(ltrim(rtrim(myfield)))
Re: "not in" doesn't work with and condition [message #371107 is a reply to message #371096] Mon, 28 August 2000 01:59 Go to previous message
arun
Messages: 67
Registered: January 2000
Member
hi,

let me know itemcode data type. if it is char or varchar2. then check out this.

and trim(items.itemcode) = '1'.

hope this will do.

rgds,

Arun
Previous Topic: Table almost too large to query
Next Topic: Duplicat rows in pl.sql ables.
Goto Forum:
  


Current Time: Wed Oct 21 11:51:52 CDT 2020