As part of what I want in a report, I want the associated row from t2,
if there is one, with the largest date in a range based on the base row
from t1. I attempted the following:

left outer join IS3.service_ai t2 on T1.dam_BHID=T2.cow_BHID
and t2.datex = (select max(datex) from is3.service_ai
where days(datex) < (days(t1.birth_date)-283+45)
and days(datex) > (days(t1.birth_date)-283-45)
)

which fails because a join condition may not have a subselect in it. I
(believe) can't move the condition to main where clause because many
rows in t1 have no corresponding rows in t2, while others have many.
Anyone have a suggestion on how this can be achieved?

Bob Stearns wrote:

left outer join IS3.service_ai t2 on T1.dam_BHID=T2.cow_BHID
and t2.datex = (select max(datex) from is3.service_ai
where days(datex) < (days(t1.birth_date)-283+45)
and days(datex) > (days(t1.birth_date)-283-45)
)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Like this?

left outer join (select * from is3.service_ai
where days(datex) < (days(t1.birth_date)-283+45)
and days(datex) > (days(t1.birth_date)-283-45)
order by datex desc fetch first ow only) as t2 on
T1.dam_BHID=T2.cow_BHID

In general (outside of max and min) you ahve teh choice of pushing the
scalar subquery predicate down into the leg (you can correlate to the
left in a left outer or an inner join) or you could use OLAP to find the
right group without 'loosing' the other columns.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

How about this?

left outer join IS3.service_ai t2 on T1.dam_BHID=T2.cow_BHID

WHERE t2.datex = (select max(datex) from is3.service_ai
where days(datex) < (days(t1.birth_date)-283+45)
and days(datex) > (days(t1.birth_date)-283-45)
)
OR t2.datax IS NULL

Tonkuma wrote:
> How about this?
>
> left outer join IS3.service_ai t2 on T1.dam_BHID=T2.cow_BHID
>
> WHERE t2.datex = (select max(datex) from is3.service_ai
> where days(datex) < (days(t1.birth_date)-283+45)
> and days(datex) > (days(t1.birth_date)-283-45)
> )
> OR t2.datax IS NULL
>
Thanks, I'll try that; the whole select (8 joins) already has a
complicated where expression, but I'll see if I can add this.

One other option to consider is the OLAP RANK function. Something like:

select ...
from (
select b.*
, rank() over (partition by T1.dam_BHID, order by t2.datex desc nulls
last) as rank_num
from (
select t1...., t2....
from ... t1
, IS3.service_ai t2
where T1.dam_BHID=T2.cow_BHID
and days(t2.datex) < (days(t1.birth_date)-283+45)
and days(t2.datex) > (days(t1.birth_date)-283-45)
union all
select t1....., t2....
from ... t1
inner join IS3.service_ai t2
on T2.cow_BHID = -99
and days(datex) = -1
) b
) a
where rank_num = 1

The post UNION subselect is there to generate a row with null values for
the t2 columns, so that if the first subquery doesn't generate a join-row
there will be a row to be ranked as 1. If you know the column types, it
would be easier to do it by suitable NULLIF expressions.

You should test the efficiency of this and the other suggestions.

James Campbell

--
Message posted via http://www.dbmonster.com