SQL correlated subqueries

[Robert Stearns]

I obviously dave the following specified incorrectly. There is only one
entity_id with 33.34 as the percent value for each bhid, as you can see
from the attached data. I expected the use of t1.bhid in each of the
three clauses to cause the correlation to take place. Where did I go
wrong? Do I have to place the entire case construct in a further level
of subselect? What would the syntax be like? The diagnostic is to the
effect that a scalar subselect returned multiple rows.

select
BHID,
case
when 0 = ( select count(*) from is3.owners where BHID=t1.bhid )
then 0
when 2 in (
select entity_id from is3.owners where BHID=t1.bhid ) then 2
else (
select ENTITY_ID from is3.owners
where BHID=t1.bhid
and STARTING <= '2004-09-01'
and (ENDING is null or ENDING >= '2004-09-01'
and PERCENT=33.34) )
end as owner
from is3.owners t1
where BHID in (
SELECT bhid
FROM is3.animal_sets
WHERE userid='jhough'
AND set_name='AN_Si res' )

*************** *************** *************** *************** *************** ***

BHID ENTITY_ID STARTING PERCENT ENDING SALE_ID
------- ------------ ----------- ---------- --------- ----------
1417 3379 2004-01-01 33.3400 (null) (null)
1417 92 2004-01-01 33.3300 (null) (null)
1417 2964 2004-01-01 33.3300 (null) (null)
1700 2652 2004-01-01 33.3400 (null) (null)
1700 392 2004-01-01 33.3300 (null) (null)
1700 3212 2004-01-01 33.3300 (null) (null)
3119 307 2004-01-01 33.3400 (null) (null)
3119 2916 2004-01-01 33.3300 (null) (null)
3119 3443 2004-01-01 33.3300 (null) (null)
6023 967 2004-01-01 33.3400 (null) (null)
6023 1778 2004-01-01 33.3300 (null) (null)
6023 2443 2004-01-01 33.3300 (null) (null)
9038 2 2004-01-01 33.3400 (null) (null)
9038 1932 2004-01-01 33.3300 (null) (null)
9038 1115 2004-01-01 33.3300 (null) (null)
10523 2 2004-01-01 33.3400 (null) (null)
10523 2036 2004-01-01 33.3300 (null) (null)
10523 1932 2004-01-01 33.3300 (null) (null)
15363 2 2004-01-01 33.3400 (null) (null)
15363 1932 2004-01-01 33.3300 (null) (null)
15363 2040 2004-01-01 33.3300 (null) (null)
21723 625 2004-01-01 33.3400 (null) (null)
21723 2529 2004-01-01 33.3300 (null) (null)
21723 3429 2004-01-01 33.3300 (null) (null)
21952 983 2004-01-01 33.3400 (null) (null)
21952 2652 2004-01-01 33.3300 (null) (null)
21952 3212 2004-01-01 33.3300 (null) (null)
22517 3052 2004-01-01 33.3400 (null) (null)
22517 2457 2004-01-01 33.3300 (null) (null)
22517 888 2004-01-01 33.3300 (null) (null)
22523 3401 2004-01-01 33.3400 (null) (null)
22523 2758 2004-01-01 33.3300 (null) (null)
22523 67 2004-01-01 33.3300 (null) (null)
22604 2647 2004-01-01 33.3400 (null) (null)
22604 507 2004-01-01 33.3300 (null) (null)
22604 2241 2004-01-01 33.3300 (null) (null)

36 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 78/ms]

[Executed: 10/10/04 3:14:15 PM EDT ] [Execution: 359/ms]

[Tokunaga T.]

Re: SQL correlated subqueries

I thought that you had better to replace ending parenthesis like this:

else (
select ENTITY_ID from is3.owners
where BHID=t1.bhid
and STARTING <= '2004-09-01'
and (ENDING is null or ENDING >= '2004-09-01') <--- To here
and PERCENT=33.34 ) <--- From here

[Tokunaga T.]

Re: SQL correlated subqueries

And,
(1) If you want only one row for each BHID, you should add conditions
on WHERE clause or specify DISTINCT.
(2) I feel following clause is not neccesary. Because, always there is
at least one row which satisfy BHID=t1.bhid.
when 0 = ( select count(*) from is3.owners where BHID=t1.bhid
)
then 0

So, my idea is the following. Does it meet your requirements?
select
BHID,
case
when 2 in (
select entity_id from is3.owners where BHID=t1.bhid )
then 2
else ENTITY_ID
end as owner
from is3.owners t1
where BHID in (
SELECT bhid
FROM is3.animal_sets
WHERE userid='jhough'
AND set_name='AN_Si res' )
and STARTING <= '2004-09-01'
and (ENDING is null or ENDING >= '2004-09-01')
and PERCENT=33.34

I assumed that there is a row that satisfy the following condition for
each BHID.
and STARTING <= '2004-09-01'
and (ENDING is null or ENDING >= '2004-09-01')
and PERCENT=33.34

If this is not true, how about this?
select DISTINCT
BHID,
case
when 2 in (
select entity_id from is3.owners where BHID=t1.bhid )
then 2
else COALESCE((
select ENTITY_ID
from is3.owners
where BHID=t1.bhid
and STARTING <= '2004-09-01'
and (ENDING is null or ENDING >= '2004-09-01')
and PERCENT=33.34 ), 0)
end as owner
from is3.owners t1
where BHID in (
SELECT bhid
FROM is3.animal_sets
WHERE userid='jhough'
AND set_name='AN_Si res' )

Or this?
(I assumed that there is a row that satisfy one of conditions
connected by OR.)
select
BHID,
ENTITY_ID as owner
from is3.owners t1
where BHID in (
SELECT bhid
FROM is3.animal_sets
WHERE userid='jhough'
AND set_name='AN_Si res' )
AND (entity_id = 2
OR
NOT EXISTS (
SELECT *
FROM is3.owners t2
where t2.BHID=t1.bhid
AND t2.entity_id = 2)
AND STARTING <= '2004-09-01'
and (ENDING is null or ENDING >= '2004-09-01')
and PERCENT=33.34
)