SQL WHERE sub clause

I need to run the statement below but I am receiving the following error message:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I know what the error is telling me (iti s returning more than one ponumber for the query).  I need to know how I would run that and show me all ponumber that match the where clause.

Any ideas?
select a.ponumber, a.itemnmbr, a.itemdesc, a.vendorid, a.unitcost, b.stndcost, b.currcost from 
(select * from pop10110) A
left join 
(select * from iv00101) B
on a.itemnmbr = b.itemnmbr
where (a.unitcost > b.stndcost) or (a.unitcost = 0) and (ponumber=(select ponumber from pop10100 where postatus = '5'))
order by ponumber asc

Open in new window

r270baAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
frankyteeConnect With a Mentor Commented:
you have = select rather than IN select, try:
select a.ponumber, a.itemnmbr, a.itemdesc, a.vendorid, a.unitcost, b.stndcost, b.currcost from
(select * from pop10110) A
left join
(select * from iv00101) B
on a.itemnmbr = b.itemnmbr
where (a.unitcost > b.stndcost) or (a.unitcost = 0) and (ponumber IN (select ponumber from pop10100 where postatus = '5'))
order by ponumber asc
0
 
bgloddeCommented:
select a.ponumber, a.itemnmbr, a.itemdesc, a.vendorid, a.unitcost, b.stndcost, b.currcost from
(select * from pop10110) A
left join
(select * from iv00101) B
on a.itemnmbr = b.itemnmbr
where (a.unitcost > b.stndcost) or (a.unitcost = 0) and (ponumber IN(select ponumber from pop10100 where postatus = '5'))
order by ponumber asc
0
 
r270baAuthor Commented:
Could I split the points with the two answers since they were both correct and one posted as I was assigning points?
0
 
bgloddeCommented:
I was just a little too slow! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.