How to join with a subquery conditional DB2 UDB 8.1.5 Linux

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 09 Apr 2005 22:52 GMT

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?

Reply to this Message

Serge Rielau - 09 Apr 2005 23:00 GMT

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)
)

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Serge Rielau - 09 Apr 2005 23:08 GMT

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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Tonkuma - 10 Apr 2005 10:59 GMT

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

Reply to this Message

Bob Stearns - 10 Apr 2005 18:30 GMT

> How about this?
>
[quoted text clipped - 5 lines]
> )
> 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.

Reply to this Message

James Campbell - 11 Apr 2005 01:10 GMT

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

Reply to this Message