Currency type conversion from SQL 1

[alastair3]

I have two SQL databases, which are supposed to be identical, however there is a subtle difference with the schema definitions during initialization, which means that in one database, one particular field is type "money" and in the other it is "dt_money_gr_cy"

In Crystal 9 these are viewed when accessing the databases through ODBC as "Currency" and "Number" respectively.

I believe in Crystal 8.5, both types were a number type which made my job a lot easier.

I can do a type conversion in my reports via tonumber(), but does anyone know how Crystal handles its type mappings and if there is any control over that?

 

[synapsevampire]

You can use a SQL Exprfession to CAST them on the database side, which will be more efficient.

CAST(table.field as money)

-k

 

[alastair3]

Thanks for the suggestion. That would certainly be more effective.

Does anyone have any idea how Crystal determines it's own 'field type' for a given field?

 

[synapsevampire]

That would be partially based on the driver used to connect to the dataqbase.

Assuming that you mean SQL Server databases (SQL is a language), you're probably using ODBC.

Since you are using CR 9, you can use the Add Command to paste in your own SQL, or as I previously suggested, you can use a SQL Expression to CAST the field to whatever data type you need.

Not sure what else you want, those are viable, easily implemented solutions, and you don't have another means to change the data types except using SQL or a formula in CR.

-k

 

[alastair3]

I know there's a particular problem with this one field. I was interested to find out from how Crystal interpreted SQL data types to alert me if there were any other fields which might have a problem. That would have been useful as we have a very complicated schema and 100's of reports that are used to obtain information from this database.

As a better solution, we are tying to ensure that both databases have precisely the same schema.

Thanks for your input.

 

[synapsevampire]

Ahhh, I see, you have it in mind to use Crystal to analyze databases.

There are lots of tools and scripts that will allow you to list out all data types for every column in the database, which you might then sort by type to determine this.

-k

 

[alastair3]

I'm aware that there are any number of tools and ways for obtaining data type information from the database, but that isn't the problem. The problem is the the type they get translated to in Crystal and how that is determined.

We run the reports against a number of databases, with an automatic change of datasource from within our software when running a Crystal report. Certain reports fail due to Crystal encountering a different type from what it was expecting. In this particular case a currency field rather than a number field.

The overall schema is the same and the types should be the same, but they're not due to slightly differing creation methods for the database. I was just trying to work out which field/column types we needed to be particularly wary of.

Clearly the correct solution is to ensure that the databases are absolutely identical in structure and that's now the only route we are pursuing.

Thanks again for your input.