I’m trying to create dynamic parameters in Crystal XI running against a MySQL 4.1 database. If I use a non-union query and bring back character fields from the database, the data type for string comes through properly. I forget what exactly it is, but it says that the datatype is a string or character or something like that.
select field1, field2
from tables
field1 and field2 from the above query are character fields in the database and show up as string.
The problem is that I need to use union queries to get the parameter values. When a union query is used the datatype returned to Crystal is a memo datatype. Unfortunately, memo isn’t a very useful data type. It can’t be used as a source for parameter selection and seems to have other limitations as well.
select field1, field2
from tables
union
select field1, field2
from tables
field1 and field2 from the above query are character datatypes in the database, but are returned to Crystal as a memo datatype, even if the queries being unioned are the exact same queries as the non-union query from above.
I’ve tried everything I can think of to have the datatype returned be a character type. I’ve tried using CAST, CONVERT, SUBSTR, LEFT, etc. I’ve also tried enclosing the union query in a select, and tried using CAST and CONVERT with that select.
Any thoughts? Thanks for any advice.
JonTarz 
(BOB member since 2006-01-09)
(BOB member since 2006-03-15)