Unable to group by fields in UNION from a Crystal Query

[StevenK]

I am trying to develop a report against a pairing of Paradox tables.
What I have done is to create a Crystal (SQL) Query (with an ODBC link to the directory where the tables are) using syntax similar to the following :

SELECT
Table1.`Field1`,
Table1.`Field2`
FROM
`Table1` Table1
UNION
SELECT
Table2.`Field1`,
Table2.`Field2`
FROM
`Table2` Table2

[Note : Both 'Field1' and 'Field2' are strings and have the same length and name in both table structures]

When I then create a Crystal Report using this Crystal Query as the basis of the data I am able to select the two resultant fields, namely 'Field1' and 'Field2'. However the two fields are not available in the 'Group' tab when stepping through the Report Expert and cannot be used when creating a new grouping through the report designer itself.

When I create a similar Crystal Query with just one of these tables, I would use syntax as follows :

SELECT
Table1.`Field1`,
Table1.`Field2`
FROM
`Table1` Table1

I then find that both fields are available for selection and are available in the 'Group' tab when stepping through the Report Export.

I can only assume that the UNION within the query syntax is causing a problem here, albeit that the field names are the same, the datatype and length being the same too.

Has anyone come across this when developing reports against Paradox tables ?
Can anyone explain why this should be the case ?
Is there any way I can work round this ?

Thanks in advance.

Steve

 

[Ngolem]

is there any relationship between these 2 tables?
I don't think you are using the SQL Query designer are you...rather you are dropping this query directly into the report...and I don't think that is legal.

Unless you wish to sort these fields somehow...one alternate approach might be to use a subreport for one set of values and the other set of values be in the main report.

Another alternative is to create a view using your union query and sample that using the report.

I assume you have oversimplified your problem for illustration purposes but another more complicated approach if you wish to shuffle the 2 tables together is to use a shared array to store the result of each table and then in footers print them out. This would work if there are relatively few values to deal with

hope this helps

Jim Broadbent

 

[rhinok]

I've actually run into this problem using Crystal Queries (Vs. dropping the SQL into the report) with SQL Server 6.5. In my particular SQL statement, I went so far as to convert the Varchar fields to Char and it still wouldn't let me group by those fields. They were treated as memos.

A view wasn't possible since I had more than 16 table joins (SQL Server 6.5 limitation). My only solution was to build a stored procedure and use this as a datasource for the report.

 

[synapsevampire]

I don't use Crystal Queries for many reasons, add this to the list I reckon...

Create an ODBC connection to the paradox tables and use it in the report, then you can drop the Union SQL into the Database->Show SQL Query window (or at
least modify to add in the UNION SELECT...)

Now group on any of the fields.

I just tested with a Paradox ODBC connection without a problem.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com