Record selection problem with string field - thinks it's UNICODE

[Former Member]

We are having a problem with a record selection in a report. The field is a string field in the table, but Crystal formats the SQL like it is a Unicode field. The documentation says something vague about the language selection on the PC, but this does not apply in this case.

The error we receive trying to refresh the data is:

Failed to retrieve data from the database.

Details: SQL Exception: [SQL State:] HY000 [Error Message:] Encountered "\'123\" at line 1, column 1381.

The SQL Crystal produced is:

SELECT ....

FROM ....

WHERE ... AND "Position"."positionDetail_account_firm"=N'123'

The problem is the "Position"."postionDetail_account_firm"=N'123' selection. We are using a JDBC driver to attach to the database. If I take the SQL, copy it into Squirrel using the same driver, and remove the 'N', the SQL works. If I can get Crystal to produce the SQL without the 'N', I am confident it will work.

Has anyone seen anything like this? Any help would be greatly appreciated.

Edited by: rbashore on Oct 15, 2009 6:02 PM

[Former Member · Accepted solution]

Can you are the DBA produce a view, and then base the report off the view.

Or perhaps create a formula to convert that string value to text.

( But no I have never seen that )

[Former Member · Accepted solution]

Thanks for the advice. I used the ToText function to create a new formula field and it worked. Strange, but it worked. Thanks.