OraFAQ Forum: Server Administration » "not in" doesn't work with and condition

Home » RDBMS Server » Server Administration » "not in" doesn't work with and condition

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

"not in" doesn't work with and condition [message #371096]

Fri, 25 August 2000 06:44

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.

Report message to a moderator

Re: "not in" doesn't work with and condition [message #371103 is a reply to message #371096]

Fri, 25 August 2000 15:23

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)))

Report message to a moderator

Re: "not in" doesn't work with and condition [message #371107 is a reply to message #371096]

Mon, 28 August 2000 01:59

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 01:21:57 CST 2025