r270ba
asked on
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Could I split the points with the two answers since they were both correct and one posted as I was assigning points?
I was just a little too slow! :)
(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