Subject [firebird-support] Re: merging multiple SELECT statements into one if possible Svein Erling Tysvær 2011-12-28T07:36:20Z
>> SELECT A2.ID FROM A2 JOIN I2 ON A2.ID = I2.ID WHERE A2.ALI = 'a0002'; /* returns 2 from A2*/
>>
>> SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 WHERE D2.ID = A2.ID AND D2.YMD = '2011-12-21'; /* return 2 from D2 */
>>
>> SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 JOIN I2 ON D2.ID = I2.ID WHERE I2.ID = 2 AND D2.YMD = '2011-12-21';

>I rewrote to make my question clearer in mixed format :
>
>For a pair (S, Y) where S is a string and Y is a date.
>
>SELECT A2.ID FROM A2 WHERE A2.ALI = <S>;
>if A2.ID exists then
> SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 WHERE D2.ID = A2.ID AND D2.YMD = <Y>;
>else
> SELECT I2.ID FROM I2 WHERE I2.INST = <S>;
> if I2.ID exists then
> SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 WHERE D2.ID = I2.ID AND D2.YMD = <Y>;
> end
>end

You could try something like:

WITH MyA2 as (SELECT A2.ID FROM A2 WHERE A2.ALI = :s),
MyI2 as (SELECT I2.ID FROM I2 WHERE I2.INST = :s)
SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB
FROM D2
LEFT JOIN MyA2 on (1=1)
LEFT JOIN MyI2 on (1=1)
WHERE D2.YMD = :Y
AND D2.ID = coalesce(MyA2.ID, MyI2.ID)

A (probably faster and more intuitive) alternative would be to use EXECUTE BLOCK:

EXECUTE BLOCK (MyString VARCHAR(32) = :S, MyYMD DATE = :Y)
RETURNS (ID INTEGER, YMD DATE, TB INTEGER, MB INTEGER, DB INTEGER)
AS
BEGIN
ID = 0;
SELECT A2.ID FROM A2 WHERE A2.ALI = :S INTO :ID;
IF (ID = 0) THEN
BEGIN
SELECT I2.ID FROM I2 WHERE I2.INST = :S INTO :ID;
END
FOR SELECT D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 WHERE D2.ID = :ID INTO :YMD, :TB, :MB, :DB DO
SUSPEND;
END

The EXECUTE BLOCK above is likely to contain some errors, I rarely use it.

HTH,
Set