--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> >> select f1, f2, f3 from t1
> >> where f1 = 'xxx'
> >> union
> >> select 0, 0, 0 from rdb$database
> >> where not exists(
> >> select f1, f2, f3 from t1
> >> where f1 = 'xxx')
> >> union
> >> select f3, f4, f5 from t2
> >
> >Thanks, this is just my expected result.
> >
> >but my query , like this,
> >
> > select f1, f2, f3 from t1 where f1 = 'xxx'
> >
> >is a very long long sql,would you have a more simple way to write?
> >
> >My English is very terrible, hope you can understand me.
>
> This question was very easy to understand (your original question was a bit harder), the answer depends on which version of Firebird you are using. If it is a recent version, then you could use CTEs, e.g.
>
> with tmp as
> (select f1, f2, f3 from t1
> where f1 = 'xxx')
>
> select * from tmp
> union
> select 0, 0, 0 from rdb$database
> where not exists(
> select * from tmp)
> union
> select f3, f4, f5 from t2
>
> This may have to be modified depending on your situation (e.g. you cannot simply change to WHERE F1 IN ('xxx', 'xxy') and get one line from t1 and one from rdb$database), it is hard to guess which changes are required without knowing more about the actual SQL statement.
>
> HTH,
> Set
>

use the tmp table, i had tried it. perhaps i hope fetch more one records, a error raised on the position of 'for'.

my fb version is 2.5.

thank you very mush for giving me lots of help.