Re: need aid with this query

"VC" <boston103_at_hotmail.com> wrote in message news:<K9kRb.158353$xy6.770548_at_attbi_s02>...
> "soni29" <soni29_at_hotmail.com> wrote in message
> news:cad7a075.0401261754.651428da_at_posting.google.com...
> > hi,
> > i need some help with a query, also to find out if this is even
> > possible with sql. currently i have a table with the following data:
> >
> > CustomerNumber CustomerBranch
> > 123 NULL
> > 123 1
> > 123 2
> > 221 NULL
> > 221 5
> > 555 1
> > 555 9
> > 125 NULL
> >
> > now in this data, CustomerNumber and CustomerBranch are the keys, each
> > customer MUST have a CustomerBranch with null, those NULL
> > CustomerBranch's represent the company headquaters, while the ones
> > with numbers are the other offices. Occassionally data is missing, in
> > the example above CustomerNumber 555 does not have a NULL
> > CustomerBranch, this is wrong. Is there anyway in SQL to find all
> > those CustomerNumbers who do not have a null, there can only be one
> > null per CustomerNumber. i was thinking about using a
> > count(CustomerBranch) but not sure how to write it to count all those
> > CustomerBranchs per CustomerNumber that are equal to 0, if that's the
> > right way to do it.
> >
> > Thank you.

and VC wrote, (I moved the top post down for context)

> Hello,
>
> SQL-92:
>
> select distinct customernumber
> from t1
> where customernumber not in (select customernumber from t1 where
> customerbranch is null)
>
> SQL-99 (probably faster):
>
> select distinct customernumber from
> (select customernumber, first_value(customerbranch) over (partition by
> customernumber order by customerbranch desc) first
> from t1)
> where first is not null
>
>
> VC
>

I've always disliked distinct in that it implies you did not have the full search criteria. If nothing else, in most queries you can change DISTINCT to a COUNT(*).
I'd solve this case something like this (uses an inline view):

select customernumber
from
(select customernumber, count(*) cnt_all , count(customerbranch) cnt_branch
from t1 ) va
where va.cnt_all = va.cnt_branch ;

This makes use of the fact that count() does not count NULL values. So if there is no main branch, then there is no record for that customer where the branch is NULL.

 HTH,
ed Received on Tue Jan 27 2004 - 20:00:58 CET