Union Query 3

[mattKnight]

Any ideas on how (or if it is possible) to create a union query from within CR9?

I am using a datasource that doesn't support views or commands. I need to combine and group several tables into a single report but it isn't possible to use joins...

It also isn't possibl;e to create the recordset externally and pass it to the report.

Any ideas?

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[AlexB1234]

I don't do alot of SQL from within Crystal but if I recall correctly you can open and edit the SQl window in crystal. If that's the case you can just type the Union query you need, e.g. Select Field1, Field2 From table1 Union Select Field1, Field2 From table2;

 

[synapsevampire]

What data source doesn't share commands?

And how would you expect a Union if Union isn't supported?

I don't have CR 9 on this box, but you should be able to use Database->Show SQL Query->Copy the query, Add the line Union, then paste the query and modify the second line as required.

-k

 

[mattKnight]

synapsevampire

My fault, the question was really unclear - even confused.... Let me try and explain

The data source is a SOP/POP system running on a UNIX server. This system uses a proprietry DB (I am not sure that it qualifies as a DB - just a loose structure of tables) to store its data.
There are ODBC drivers to connect this UNIX server to MS systems. We are using these to extract data for CR purposes.

So far It is clear (i hope)

>I am using a datasource that doesn't support views or commands.

What I meant is that there doesn't appear to be be a way to create a server side view or stored procedure. (I got confused with ADODB Commands).

To use the word support is incorrect....


I can't uses joins because the tables do not share a key


I hope that clears it up.... (if not I'll try to explain more)

Thank you both for the advice, I knew you could view the SQL statement, but I didn't know you could cut and paste.

I think that will solve the problem

I'll test and get back to you


Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[bdreed35]

Matt:

If you are using CR9, why don't you try the SQL Command feature. I haven't really used it but it is my understanding that you should be able to write any SQL in there that you need to.

~Brian

 

[synapsevampire]

Brian: That's true, but the solution proposed is identical, it forces a specific Union to be passed in the SQL.

-k

 

[IdoMillet]

Assuming a UNION is not directly suported by the data source, I would create an Access database and use
File, Get External Date, LINK Tables...
pick the ODBC as the extyernal file type and link to the necessary tables in the data source.

Then create the UNION query in Access and use it as the data source for the Crystal report.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[mattKnight]

Thank you all for your responses.

As the project progresses, it ha sbecome clear that the linking the tables through access is the solution that best suits.

Thanks again and *s all round

Take Care

Matt
If at first you don't succeed, skydiving is not for you.