Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unable to group by fields in UNION from a Crystal Query

Status
Not open for further replies.

StevenK

Programmer
Jan 5, 2001
1,294
GB
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
 
I responded to your other post:

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
kai@informeddatadecisions.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top