Prev: Case sensitivity
Next: Weird deadlock issue
From: brunoalsantos on 2 Aug 2010 09:41
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.
From: danfan46 on 2 Aug 2010 10:20
On 2010-08-02 15:41, brunoalsantos wrote:
> 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.

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
From: Tonkuma on 2 Aug 2010 10:32
> 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> )
>
Select * from A
where <cond>
union all
select * from B
where <cond>
and NOT EXISTS
(select 0 from A where <cond> )

From: Tonkuma on 2 Aug 2010 11:15
> Select * from A
>  where <cond>
> union all
> select * from B
>  where <cond>
>    and NOT EXISTS
>        (select 0 from A where <cond> )
In this case, UNION ALL can be replaced by FULL OUTER JOIN, like this:

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
;
From: ChrisC on 2 Aug 2010 14:33
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).
 |  Next  |  Last
Pages: 1 2
Prev: Case sensitivity
Next: Weird deadlock issue