Hi all,

DB2 LUW 9.5 fixpack 5.

Scenario:

select <result> from A, B where ....

If A contains at least one row, return A.* else B.*

Is there a way to do this in SQL?

Thanks in Advance.

Bruno.

On 2010-08-02 15:41, brunoalsantos wrote:
Assuming that columns in table A and B are equivalent.

Select * from A
where <cond>
and 1 >= (select count(*) from A where <cond> )
union all
select * from B
where <cond>
and 0 = (select count(*) from A where <cond> )

/dg

where <cond>
union all
select * from B
where <cond>
and NOT EXISTS
(select 0 from A where <cond> )

SELECT COALESCE(a.col_1 , b.col_1) AS col_1
, COALESCE(a.col_2 , b.col_2) AS col_2
....
, COALESCE(a.col_n , b.col_n) AS col_n
FROM (SELECT * FROM A WHERE <cond>) A
FULL OUTER JOIN
(SELECT *
FROM B
WHERE <cond>
AND NOT EXISTS
(SELECT 0 FROM A WHERE <cond>)
) B
ON 0=0
;

Using rank() and union all:

select * From (
select *, rank() over(order by table_order) as rank from(
Select *, 1 as table_order from A
where <cond>
union all
select *, 2 as table_order from B
where <cond>
) x
) y
where rank = 1

You'll probably want to call out the columns in the outer select
specifically (to at least get rid of the table_order and rank
columns).

On 2 ago, 15:33, ChrisC <cunningham... (AT) gmail (DOT) com> wrote:
Thank you danfan46, Tonkuma and Chris.