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
LVL 1
rgb192Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
update p set
        p.ourinwarehouse = p.inwarehouse - ISNULL(P_Cnt.prod_Cnt,o)
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
0
 
halfbloodprinceConnect With a Mentor Commented:
In your sub query make this change:
select p2.productid, IsNull(count(*),0) As 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
0
 
rgb192Author Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.