--- In [email protected], 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.
