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(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.

[VC]

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

"soni29" <so****@hotmail.com> wrote in message
news:ca**************************@posting.google.c om...

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.

[Ed prochak]

"VC" <bo*******@hotmail.com> wrote in message news:<K9kRb.158353$xy6.770548@attbi_s02>...

"soni29" <so****@hotmail.com> wrote in message
news:ca**************************@posting.google.c om...

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

[VC]

Hello Ed,

The problem with your query is that it does not work:

SQL> create table t1(CustomerNumber int, CustomerBranch int);

Table created.

SQL> insert into t1 values(123, NULL);

1 row created.

SQL> insert into t1 values(123, 1);

1 row created.

SQL> insert into t1 values(123, 2);

1 row created.

SQL> insert into t1 values(221, NULL);

1 row created.

SQL> insert into t1 values(221, 5);

1 row created.

SQL> insert into t1 values(555, 1);

1 row created.

SQL> insert into t1 values(555, 9);

1 row created.

SQL> insert into t1 values(125, NULL
2
SQL> select customernumber
2 from
3 (select customernumber, count(*) cnt_all , count(customerbranch)
4 cnt_branch
5 from t1 ) va
6 where va.cnt_all = va.cnt_branch ;
(select customernumber, count(*) cnt_all , count(customerbranch)
*
ERROR at line 3:
ORA-00937: not a single-group group function
SQL>
VC
"Ed prochak" <ed********@magicinterface.com> wrote in message
news:4b**************************@posting.google.c om...

"VC" <bo*******@hotmail.com> wrote in message

news:<K9kRb.158353$xy6.770548@attbi_s02>...

"soni29" <so****@hotmail.com> wrote in message
news:ca**************************@posting.google.c om...

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

[kibeha]

Hi

I'd use :

select
customernumber,
count(decode(customerbranch,null,1,null)) number_of_null_branches
from t1
group by customernumber
having count(decode(customerbranch,null,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