need aid with this query

[soni29]

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(CustomerB ranch) 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.

[kibeha]

Re: need aid with this query

Hi

I'd use :

select
customernumber,
count(decode(cu stomerbranch,nu ll,1,null)) number_of_null_ branches
from t1
group by customernumber
having count(decode(cu stomerbranch,nu ll,1,null)) != 1;

The count(decode()) syntax will count null values in the
customerbranch column.
The having clause ensures that you only get customernumbers with
either zero or 2 or more null-values - so it also checks your
requirement of only one null value :-)


Regards

KiBeHa