Link options

[loRdK]

I'm using Crystal Reports 4.6.1 with an ODBC database.

I need to use the option "Link options/Look up all of one, then all of other", but it's not available for ODBC databases.

Can anyone tell me how to reproduce that behaviour for an ODBC database?

Thanks.

 

[synapsevampire]

I can't recall what that option does, but it sounds like the equivalent of a Union.

Go into the Database->Show SQL Query

Copy the existing query

Type the word Union at the end

Paste in the query and change the name of the table used to the second table.

Now you'll get all of the first table and all of the second table.

This would be better to create on the database as a View.

-k

 

[loRdK]

Thanks, synapsevampire.

But the union query is not possible because the select statements I need have different number of columns.

Any other idea?

 

[IdoMillet]

Add to the UNION part that has less columns dummy columns.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[loRdK]

Ido, I have no dummy columns, the tables are really different.

 

[loRdK]

Ido, I have no dummy columns, the tables are really different.
Thanks.

 

[dtempleton]

You're trying to link up these two tables, there must be some fields within each that you are interested in using. So just concern yourself with those fields.

Start with one of the tables and make a view with just those fields in it. The resulting SQL will look something like "Select field1, field2 from table1". Try it out and make sure it works. Now add the dummy fields to the view, such as "Select field1, field2, Null AS field3 from table1". When you test it now, you'll see that dummy field without any data in it. Add Union (or UNION ALL) at the end of the statement, and build the second query using the same fields and field names. It should now work.

 

[IdoMillet]

Dummy columns are not actual columns in the table - they are just expressions that return a constant value.

By the way, the same technique is VERY useful in adding a column that differentiates between rows that were returned in the 1st part of the UNION and rows that were returned in the 2nd part (simply use a different constant in each part).

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com