Handling Unions

[kskid]

Crystal 8.5
Oracle 8i
ODBC connectivity

Unions have been a pain in the rear to handle in Crystal without having to go into Show SQL Query and input everything. In my case, given a list of names in a table, I had to find any activity on an individual within the last week.

Anyway, I have the following in Visual Linking

NameTable.id -> LOJ -> Table1.id
-> LOJ -> Table2.id
-> LOJ -> Table3.id

and the following in Record Selection

(isnull{table1.id} or
table1.date in [Minimum(lastfullweek) to
maximum(lastfullweek)]) and
(isnull{table2.id} or
table2.date in [Minimum(lastfullweek) to
maximum(lastfullweek)]) and
(isnull{table3.id} or
table3.date in [Minimum(lastfullweek) to
maximum(lastfullweek)]) and
not(
isnull({table1.id}) and
isnull({table2.id}) and
isnull({table3.id})
)

This last part discards all names records if there are no hits on table1, table2 and table3. This leaves me with just the names that had activity.

I check the Show SQL Query and everything from the Records selection appears in the SQL.

Any comments?

It runs faster, too.

Just thought I'd pass it on.

 

[synapsevampire]

That's not the same as a Union.

If the other tables don't have identical IDs as the NameTable, you won't get them, which is generally the purpose of a Union.

What you have here is a Parent table with 3 child tables that you wanted to see all matyching rows for, when all 3 child tables have rows.

Looks like the right way to do it.

Now that you've seen the SQL created, consider Copying and pasting it into a View on Oracle, that way you'll enjoy reusability and simpler maintenance.

-k