Help using columns from Left Outer Join in query

[Todd]

Data related to the query I'm working on is structured such that
TableA and TableB are 1-many(optional). If an item on TableA has
children on TableB, I need to use the Max(tstamp) from Table B in a
condition, otherwise I need to use a tstamp from TableA (note:there
are additional tables and conditions for this query, but this problem
is based around these 2). I attempted having TableB (as B) "left
outer joined" to TableA, and a condition in the query that resembles:
Where B.Tstamp = (select MAX(tstamp) from TableB
where pkey = B.pkey)

However, the obvious problem, is that when B.Tstamp is Null, the
condition fails because the subselect fails. Is there a way I can get
the subselect to return a Null instead of failing, or a cleaner way to
be able to evaluate this condition and still handle when B.Tstamp is
Null and when it is not?

thanks in advance

[Philip Sherman]

with t1 (tstamp,pkey) as
(select max(tstamp),pkey from tableb group by pkey)
select a.pkey, coalesce(b.tstamp,a.tstamp)
from tableA a
left outer join t1 b
on b.pkey = a.pkey

will do what you want but may not perform well depending on table sizes.
If the timestamps on tableb are ALWAYS greater than tablea then:

select a.pkey, max(coalesce(b.tstamp,a.tstamp))
from tablea a
left outer join tableb b
on b.pkey = a.pkey
group by a.pkey

You may want to try a global temporary table with an index to get better
performance with the (first example) join. Check explains for different
approaches and see which yields the best performance.

Phil Sherman
Todd wrote:

Data related to the query I'm working on is structured such that
TableA and TableB are 1-many(optional). If an item on TableA has
children on TableB, I need to use the Max(tstamp) from Table B in a
condition, otherwise I need to use a tstamp from TableA (note:there
are additional tables and conditions for this query, but this problem
is based around these 2). I attempted having TableB (as B) "left
outer joined" to TableA, and a condition in the query that resembles:
Where B.Tstamp = (select MAX(tstamp) from TableB
where pkey = B.pkey)

However, the obvious problem, is that when B.Tstamp is Null, the
condition fails because the subselect fails. Is there a way I can get
the subselect to return a Null instead of failing, or a cleaner way to
be able to evaluate this condition and still handle when B.Tstamp is
Null and when it is not?

thanks in advance

[Tokunaga T.]

I think when B.Tstamp is NULL, your subselect returns NULL.
But, "NULL = NULL" is UNKNOWN(not TRUE), then the row will not be
included in result set.
Another consideration is that it might be better to correlate with A,
if possible.

So, my idea is the following:
WHERE (B.tstamp
= (SELECT MAX(tstamp)
FROM TableB
WHERE pkey = A.pkey
)
OR
B.tstamp IS NULL
)

[Tokunaga T.]

This is another idea.
SELECT ...
, COALESCE(B.tstamp, A.tstamp)
FROM TableA A
LEFT OUTER JOIN
TABLE
(SELECT ...
FROM TableB B
WHERE B.pkey = A.pkey
AND B.tstamp
= (SELECT MAX(tstamp)
FROM TableB Bm
WHERE Bm.pkey = A.pkey
)
) B
ON 0 = 0

If you could select small number of rows in TableA, this would be perform well.

[Todd]

This worked great. Thanks!

If the timestamps on tableb are ALWAYS greater than tablea then:

select a.pkey, max(coalesce(b.tstamp,a.tstamp))
from tablea a
left outer join tableb b
on b.pkey = a.pkey
group by a.pkey

[AK]

to********@yahoo.com (Todd) wrote in message news:<28*************************@posting.google.c om>...

Data related to the query I'm working on is structured such that
TableA and TableB are 1-many(optional). If an item on TableA has
children on TableB, I need to use the Max(tstamp) from Table B in a
condition, otherwise I need to use a tstamp from TableA (note:there
are additional tables and conditions for this query, but this problem
is based around these 2). I attempted having TableB (as B) "left
outer joined" to TableA, and a condition in the query that resembles:
Where B.Tstamp = (select MAX(tstamp) from TableB
where pkey = B.pkey)

However, the obvious problem, is that when B.Tstamp is Null, the
condition fails because the subselect fails. Is there a way I can get
the subselect to return a Null instead of failing, or a cleaner way to
be able to evaluate this condition and still handle when B.Tstamp is
Null and when it is not?

thanks in advance

I'd rewrite the query using a table expression instead:

FROM
....
(SELECT TABLEA.TABLEA_PK, MAX(TABLEB.TSTAMP) TSTAMP
FROM TABLEA JOIN TABLEB ON TABLEA.TABLEA_PK = TABLEB.TABLEA_PK
GROUP BY TABLEA.TABLEA_PK
UNION ALL
SELECT TABLEA.TABLEA_PK, TABLEA.TSTAMP
FROM TABLEA
WHERE NOT EXISTS(SELECT * FROM TABLEB WHERE TABLEA.TABLEA_PK =
TABLEB.TABLEA_PK)
)MY_EXPRESSION,
....
WHERE TABLEA.TABLEA_PK = MY_EXPRESSION.TABLEA_PK
AND TABLEC.SOME_COLUMN = MY_EXPRESSION.TSTAMP

alternatively, one could use a CASE expression:
WHERE
....
TABLEC.SOME_COLUMN = CASE WHEN EXISTS(
SELECT * FROM TABLEB WHERE TABLEA.TABLEA_PK = TABLEB.TABLEA_PK)
THEN (SELECT MAX(TABLEB.TSTAMP) FROM TABLEB WHERE TABLEA.TABLEA_PK =
TABLEB.TABLEA_PK
ELSE TABLEA.TSTAMP
END
....

Good luck!