Solved

how return 0 is value is null

Posted on 2011-03-21
10
193 Views
Last Modified: 2012-06-27
I have the following which works fine. However if the following sub query returns null then my results are incorrect. How can i ensure the "Sum" keyword return 0 is there are no results.
(select Sum(oi.Quantity)
             from   OrderItems oi
                    inner join Orders o
                      on oi.OrderId = o.Id
             where  oi.OrderItemStatusId < 2
                    and o.OrderStatus = 3
                    and oi.ProductId = p.Id)

Open in new window



Here is the full query
 
(select Sum(oi.Quantity) as available from
 OrderItems oi inner join Orders o on oi.OrderId = o.Id where (oi.OrderItemStatusId = 0 or oi.OrderItemStatusId = 1)  and oi.ProductId = 80493)
 
 
 SELECT p.Id                                   as Id,
       p.Name,
       CustomerSku,
       p.ProductKey,
       p.MinQuantity,
       p.MaxQuantity,
       p.Stock,
       p.IsStockItem,
       p.ShowStockPrice,
       ImageThumbUrl,
       p.Teaser,
       p.Description,
       (p.Stock
          - (select Sum(oi.Quantity)
             from   OrderItems oi
                    inner join Orders o
                      on oi.OrderId = o.Id
             where  oi.OrderItemStatusId < 2
                    and o.OrderStatus = 3
                    and oi.ProductId = p.Id)) as ProjectedStock
FROM   ProductCategories pc
       inner join Products p
         on pc.ProductId = p.Id
WHERE  pc.CompanyId = 27
       and pc.CategoryId = 138
       and p.Archive = 0
       and p.Disable = 0
       and p.AutoDisable = 0
       and EXISTS (select *
                   from   RetailerInGroup rig
                   where  (rig.RetailerGroupId = p.RetailerGroupId
                           and rig.RetailerId = 13)
                           or p.RetailerGroupId is Null)

Open in new window

 
0
Comment
Question by:frosty1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35179665
(select

case when Sum(oi.Quantity) is NULL then 0 End
             from   OrderItems oi
                    inner join Orders o
                      on oi.OrderId = o.Id
             where  oi.OrderItemStatusId < 2
                    and o.OrderStatus = 3
                    and oi.ProductId = p.Id)
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35179667
isnull((select Sum(oi.Quantity)
             from   OrderItems oi
                    inner join Orders o
                      on oi.OrderId = o.Id
             where  oi.OrderItemStatusId < 2
                    and o.OrderStatus = 3
                    and oi.ProductId = p.Id), 0)
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35179669
(select

case when Sum(oi.Quantity) is NULL then 0 End as available
             from   OrderItems oi
                    inner join Orders o
                      on oi.OrderId = o.Id
             where  oi.OrderItemStatusId < 2
                    and o.OrderStatus = 3
                    and oi.ProductId = p.Id)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 10

Accepted Solution

by:
John Claes earned 500 total points
ID: 35179672
replace in the Sum(oi.Quantity)
into 1 of the folowing


Sum(case when oi.Quantity is null then 0 else oi.Quantity end )  
Or
Sum(ISNULL (  oi.Quantity ,0))

They do the same

poor beggar

0
 
LVL 10

Expert Comment

by:John Claes
ID: 35179676
The reason why I change the check inside the SUM


4 + 5 + Null = null
4 + 5 + 0 = 9
0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 35179712
You can check like this.....

 set @seta=((select Sum(oi.Quantity)
             from   OrderItems oi
                    inner join Orders o
                      on oi.OrderId = o.Id
             where  oi.OrderItemStatusId < 2
                    and o.OrderStatus = 3
                    and oi.ProductId = p.Id)
 );
select @seta;
if @seta >0
return @seta
else
return 0;
.....you can perform the desired operations in the if and else statements...
Let me know if you want any further help...
Regards
Ramki
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 35181713
poor_beggar,

The only case when a sum can result in NULL is when there are no records to sum up:
    select sum(a) from (select 1 as a union all select 2 union all  select null ) a
will result in 3
 
   select sum(a) from (select 1 as a from syscolumns where 1=0) a
will result in NULL
0
 
LVL 21

Expert Comment

by:Amitkumar Panchal
ID: 35196311
Check using NVL() function.

(select nvl(Sum(oi.Quantity),0) as available from
 OrderItems oi inner join Orders o on oi.OrderId = o.Id where (oi.OrderItemStatusId = 0 or oi.OrderItemStatusId = 1)  and oi.ProductId = 80493)


Following is the updated full query.

SELECT p.Id                                   as Id,
       p.Name,
       CustomerSku,
       p.ProductKey,
       p.MinQuantity,
       p.MaxQuantity,
       p.Stock,
       p.IsStockItem,
       p.ShowStockPrice,
       ImageThumbUrl,
       p.Teaser,
       p.Description,
       (p.Stock
          - (select nvl(Sum(oi.Quantity),0)  
           from   OrderItems oi
                    inner join Orders o
                      on oi.OrderId = o.Id
             where  oi.OrderItemStatusId < 2
                    and o.OrderStatus = 3
                    and oi.ProductId = p.Id)) as ProjectedStock
FROM   ProductCategories pc
       inner join Products p
         on pc.ProductId = p.Id
WHERE  pc.CompanyId = 27
       and pc.CategoryId = 138
       and p.Archive = 0
       and p.Disable = 0
       and p.AutoDisable = 0
       and EXISTS (select *
                   from   RetailerInGroup rig
                   where  (rig.RetailerGroupId = p.RetailerGroupId
                           and rig.RetailerId = 13)
                           or p.RetailerGroupId is Null)
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 35196901
amit_n_panchal,

NVL is Oracle, we are talking 'bout MSSQL here.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 35196936
BTW, such a subselect in the columns part usually performs bad. It is much better to use a derived table here (you could also do with CTEs if MSSQL 2005 or above):
SELECT p.Id                                   as Id,
       p.Name,
       CustomerSku,
       p.ProductKey,
       p.MinQuantity,
       p.MaxQuantity,
       p.Stock,
       p.IsStockItem,
       p.ShowStockPrice,
       ImageThumbUrl,
       p.Teaser,
       p.Description,
       (p.Stock - isnull(ordered.quantity,0)) as ProjectedStock
FROM   ProductCategories pc
join Products p
         on pc.ProductId = p.Id
left join (select oi.ProductId, Sum(oi.Quantity) as quantity
             from   OrderItems oi
             join Orders o
               on oi.OrderId = o.Id
             where  oi.OrderItemStatusId < 2 and o.OrderStatus = 3) ordered
         on ordered.ProductId = p.Id
WHERE  pc.CompanyId = 27
       and pc.CategoryId = 138
       and p.Archive = 0
       and p.Disable = 0
       and p.AutoDisable = 0
       and EXISTS (select *
                   from   RetailerInGroup rig
                   where  (rig.RetailerGroupId = p.RetailerGroupId
                           and rig.RetailerId = 13)
                           or p.RetailerGroupId is Null)

Open in new window

0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question