rgb192
asked on
when productid count is null have product count = 0
combine these two queries
first query finds
productid count
and sets p.ourinwarehouse = p.inwarehouse - productid count
but if productid count is NULL (meaning there are no products to count)
then p.ourinwarehouse is set to NULL which is wrong
p.ourinwarehouse = p.inwarehouse - productid count
1 = 7 - 6 correct
null = 22 - null WRONG should be 22=22-0
so what I want is when productid count is null have product count = 0
update p set
p.ourinwarehouse = p.inwarehouse - P_Cnt.prod_Cnt
from products p
left outer join
(
select p2.productid, count(*) prod_Cnt from orderitems i
inner join orders o on o.orderid = i.orderid
right outer join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
group by p2.productid
) P_Cnt on p.productid = P_Cnt.productid
where p.folderid = 119 and p.ourinwarehouse2 is null
update products p set p.ourinwarehouse = p.inwarehouse where p.ourinwarehouse is null and p.folderid=119
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER