CODEWITHSUNDEEP
sql
Wally
Wally

Reputation: 21

SQL Having Clause

I'm trying to get a stored procedure to work using the following syntax:

select  count(sl.Item_Number)
as NumOccurrences 
from spv3SalesDocument as sd 
 left outer join spv3saleslineitem as sl on sd.Sales_Doc_Type = sl.Sales_Doc_Type and 
 sd.Sales_Doc_Num = sl.Sales_Doc_Num
where 
 sd.Sales_Doc_Type='ORDER' and 
 sd.Sales_Doc_Num='OREQP0000170' and 
 sl.Item_Number = 'MCN-USF' 
group by 
 sl.Item_Number 
having count (distinct sl.Item_Number) = 0

In this particular case when the criteria is not met the query returns no records and the 'count' is just blank. I need a 0 returned so that I can apply a condition instead of just nothing.

I'm guessing it is a fairly simple fix but beyond my simple brain capacity.

Any help is greatly appreciated.

Wally

Upvotes: 2

Views: 364

Answers (3)

Andomar
Andomar

Reputation: 238296

This combination of group by and having looks pretty suspicious:

 group by sl.Item_Number
 having count (distinct sl.Item_Number) = 0

I'd expect this having condition to approve only groups were Item_Number is null.

To always return a row, use a union. For example:

select  name, count(*) as CustomerCount
from    customers
group by
        name
having  count(*) > 1
union all
select  'No one found!', 0
where not exists
        (
        select  *
        from    customers
        group by
                name
        having  count(*) > 1
        )

Upvotes: 0

sfuqua
sfuqua

Reputation: 5863

First, having a specific where clause on sl defeats the purpose of the left outer join -- it bascially turns it into an inner join.

It sounds like you are trying to return 0 if there are no matches. I'm a T-SQL programmer, so I don't know if this will be meaningful in other flavors... and I don't know enough about the context for this query, but it sounds like you are trying to use this query for branching in an IF statement... perhaps this will help you on your way, even if it is not quite what you're looking for...

IF NOT EXISTS (SELECT 1 FROM spv3SalesDocument as sd 
            INNER JOINs pv3saleslineitem as sl on sd.Sales_Doc_Type = sl.Sales_Doc_Type 
                                        and sd.Sales_Doc_Num = sl.Sales_Doc_Num 
            WHERE sd.Sales_Doc_Type='ORDER' 
                and sd.Sales_Doc_Num='OREQP0000170' 
                and sl.Item_Number = 'MCN-USF')
BEGIN
    -- Do something...
END

Upvotes: 1

Nate Pinchot
Nate Pinchot

Reputation: 3318

I didn't test these but off the top of my head give them a try:

select ISNULL(count(sl.Item_Number), 0)  as NumOccurrences

If that one doesn't work, try this one:

select
  CASE count(sl.Item_Number)
    WHEN NULL THEN 0
    WHEN '' THEN 0
    ELSE count(sl.Item_Number)
  END as NumOccurrences

Upvotes: 0

Related Questions