Hi all,
This is avery unique problem. I have following strucure

fac is parent of fac_func, st_cont and org. I want know wether any of these unions return a record.

I want to stop at the first query which returns result among all the unions i.e If first union returns result than I don't want to fire second union query.

How to do that????

SELECT 1 FROM fac fa, fac_func ff

WHERE fa.fa_rec_key = 101 AND fa.fa_fac_lk = ff.fa_fac_lk

AND ( ( fa.fa_rec_effect_dt <= ff.ff_rec_effect_dt AND

( fa.fa_rec_expiry_dt IS NULL OR fa.fa_rec_expiry_dt >= ff.ff_rec_effect_dt ) )

OR ( ff.ff_rec_effect_dt <= fa.fa_rec_effect_dt AND

( ff.ff_rec_expiry_dt is null OR ff.ff_rec_expiry_dt >= fa.fa_rec_effect_dt ) ) )

UNION

SELECT 1 FROM fac fa, st_cont sc

WHERE fa.fa_rec_key = 101 AND fa.fa_fac_lk = sc.fa_fac_lk

AND ( ( fa.fa_rec_effect_dt <= sc.sc_rec_effect_dt AND

( fa.fa_rec_expiry_dt IS NULL OR fa.fa_rec_expiry_dt >= sc.sc_rec_effect_dt ) )

OR ( sc.sc_rec_effect_dt <= fa.fa_rec_effect_dt AND

( sc.sc_rec_expiry_dt IS NULL OR sc.sc_rec_expiry_dt >= fa.fa_rec_effect_dt ) ) )

UNION

SELECT 1 FROM fac fa, org ofa

WHERE fa.fa_rec_key = 101 AND fa.fa_fac_lk = ofa.fa_fac_lk

AND ( ( fa.fa_rec_effect_dt <= ofa.ofa_rec_effect_dt AND

( fa.fa_rec_expiry_dt IS NULL OR fa.fa_rec_expiry_dt >= ofa.ofa_rec_effect_dt ) )

OR ( ofa.ofa_rec_effect_dt <= fa.fa_rec_effect_dt AND

( ofa.ofa_rec_expiry_dt IS NULL OR ofa.ofa_rec_expiry_dt >= fa.fa_rec_effect_dt ) ) )