Union

building reports crystal reports
1

Is there any way we could take union of two tables from a database without using command in the report :?:

spark_saaar (BOB member since 2006-09-09)

2

You can use database views or command objects. You might be able to somewhat replicate the UNION statement with subreports also, if your report is simple enough.

mke01 :us: (BOB member since 2006-08-22)

3

HI ,
try this :
if the option ‘full outer join’ is available for use (depends on database type) u may use it to connect 2 tables u want to union and for each fields make a formula : if isnull(table1.key_field ) then table2.field
else
table1.field

please let me know if it works

Nosh

nosh (BOB member since 2006-08-29)

4

I know you can simulate a full outer join by union-ing a left and a right outer join (assuming your database doesn’t support full outer joins), but can you do the reverse?

mke01 :us: (BOB member since 2006-08-22)

5

mke01, what do u mean by ’ reverse ’ ?

nosh (BOB member since 2006-08-29)

6

I just meant “can you use a full outer join to simulate a union?” I messed around with it a bit but couldn’t figure out how to get it to work. A full outer join will include all the rows from the table1 and table2 where table1.col1 = table2.col1, plus all rows where table1.col1 is null, plus all rows where table2.col1 is null. But a union doesn’t have a join between the two tables.

mke01 :us: (BOB member since 2006-08-22)

7

union is like to combine lists , to make one list of records out to 2 lists :
select ‘f’ as type,fathet_id as id ,father_name as name from fathers
union
select ‘m’ as type,mother_id as id,mother_name as name from mothers

u get a list of fathers and mothers.

type id name

f 1 aaaaa
f 2 bbbbb
f 3 ccccc
m 4 ggggg
m 5 jjjjjjjj

but as described before, when u don’t/can’t use command in crystal
u try to be as creative as u can
so if your database allows use full outer join :
(sql server syntax
select * from fothers full outer join mothers fathers.id=mothers.id)
you get :

fathers.type|moms.type|fathers.id|moms.id|fathers.name|moms.name
| | | | |
f | null | 1 | null | aaaaa | null
f | null | 2 | null | bbbbb | null
f | null | 3 | null | ccccc | null
null | m | null | 4 | null | ggggg
null | m | null | 5 | null | jjjjjjjj

now make formula fields :

type : if isnull({fathers.type}) then {moms.type}
else
{fathers.type}

id :
if isnull({fathers.type}) then {moms.id}
else
{fathers.id}

name :

if isnull({fathers.type}) then {moms.name}
else
{fathers.name}

I hope this helps

i can send a report sample if u want

Nosh

nosh (BOB member since 2006-08-29)

8

Hey Nosh,

I just tried this out: great tip, and thanks for sharing. One final question: is this something you regularly use? Are there any kinds of issues with this method that you’ve run across? I googled things like “full outer join performance issues” and didn’t run across too much. I still think views are the best option, but the full outer join seems better than using command objects, which I strongly disfavor.

mke01 :us: (BOB member since 2006-08-22)

9

some times command is the best option , for example the case when a sub query requiers parameters ,and when the dba is too buzy to create a view …:slight_smile: (assuming u do not have full access to the database).

nosh (BOB member since 2006-08-29)