>Thank you Ann, Thank you Set,
>yes, in my real situation I have to extend the query to other fields from T2 -
>therefore the second solution seems to be the better one for me.
>Could "Common Table Expressions" or "Derived Tables" bring any advantages?
I suppose you could use a combination of the two suggestions, Josef:
with tmp(t1, t2a, t2b) as
(select T1.T1, min(T2.T2), max(T2.T2)
from T1
join T2 on T1.T1 = T2.T1
group by T1.T1)
select tmp.t1, tmp.t2a, tmp.t2b, <whatever>
from tmp
left join t2 t2_1 on tmp.t2a = t2_1.t2
left join t2 t2_2 on tmp.t2b = t2_2.t2
and t2a.t2 < t2b.t2 --the 'and' part is only required if there
may be only one matching t2.t2
The one advantage that immediately springs to mind, is that this would get a
result if for some reason there were one or three matches, not exactly two
(though in the case of three matches, only the first and last would be
returned). Sometimes it can also make complex or repetitive calculations more
readable.
EXECUTE BLOCK RETURNS(T1 INTEGER, T2a INTEGER, T2b INTEGER) as
BEGIN
FOR SELECT T1 FROM T1 INTO :T1 DO
BEGIN
SELECT MIN(T2), MAX(T2)
FROM T2
WHERE T1 = :T1
INTO :T2a, T2b:
SELECT <whatever>
FROM T2
WHERE T2 = :T2_a
INTO <other T2a output parameters>;
IF (T2a = T2b) THEN
BEGIN
<all T2b parameters> = NULL;
END
ELSE
BEGIN
SELECT <whatever>
FROM T2
WHERE T2 = :T2_b
INTO <other T2b output parameters>;
END
SUSPEND;
END
END
is also an alternative. Admittedly, in your particular example, I don't see
much point in using it. However, I do find that EXECUTE BLOCK sometimes make
complex queries look a lot simpler, so it is worth considering as an
alternative if there's considerably more complexity in your real world case.
HTH,
Set
