Problems when I use UNION

[jdulac]

After I use the UNION satement in my SQL Query I can not group by any of the text fields in Crystal Reports version 8.0. Am I doing something wrong? When I try removing the UNION statement along with the next select I am fine.

 

[IdoMillet]

How did you create your UNION SQL query?
Were you using Crystal SQL Designer?

It may be easier to help you if you show your
UNION query.

Cheers,
- Ido ixm7@psu.edu

 

[jdulac]

This is just a quick query I was trying to test it. When I tried creating a report from this query I could not access "Name" when I go to create group. If I leave the second select statement out I can access "Name" without a problem. I am using Crystal SQL Designer.

SELECT
People.`Name`,
People.`Age`,
People.`Sex`
FROM
`People` People
WHERE
People.`Sex` = 'M'
UNION
SELECT
People.`Name`,
People.`Age`,
People.`Sex`
FROM
`People` People
WHERE
People.`Sex` = 'F'

 

[Tmorton]

Did you make any progress on this? Maybe you could try giving your fields an explicit alias using the AS keyword?

Like:
SELECT
People.`Name` <b>as Name</b>,
People.`Age` <b>as Age</b>,
People.`Sex` <b>as Sex</b>
FROM
`People` People
WHERE
People.`Sex` = 'M'
UNION
SELECT
People.`Name` <b>as Name</b>,
People.`Age` <b>as Age</b>,
People.`Sex` <b>as Sex</b>
FROM
`People` People
WHERE
People.`Sex` = 'F'

 

[jdulac]

I think it may have something to do with the problem with the UNION statement and text fields where it converts the text field to a memo field after a UNION. I read something about this on the forum on version 7, you would think Crystal would have corrected this problem by now. I'm trying the fix with an embedded query but I'm unsure how to use a query as a source file for another query in Crystal SQL designer.

 

[dbielenda]

I do not know if this will help you much, however, I had a similar issue. I remedied it by creating a view in Oracle. The report worked fine after that.

 

[jdulac]

PS. The database that I am working with was created in Access 2000. I tried the same thing on a btreive database and I not have the same issue. Unfortunately I need it to work on access 2000.

 

[Ngolem]

I don't think your FROM statement is right.

It should have the database, tacked onto the table name

eg. database.'table' table

something like that ...it has been a while


My trick for using SQL Designer was to create a basic query first through simple linking of the tables..(or in your case just selecting fields in the one table....

then look at how Crystal composed its query and then use that format to finish it as a union query...(then all of the query wizard tools dissappear.)