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