SAP Knowledge Base Article - Public

1415792 - Crystal Report 2008 query can't return the correct result against Oracle 10g

Symptom

  • Crystal Report(CR) 2008 query can't return the correct result against Oracle 10g.
  • The comparison of ASCII does't work.

Environment

  • Operation System(OS): Windows 2003 server SP2
  • CR: 2008 SP1
  • Database(DB): Oracle 10g

Reproducing the Issue

  1. Create table with a nvarchar field under Oracle 10g.
  2. Fill the field with three values, i.e ‘a’, ‘b’ & ‘Z’.
  3. Setup a CR report against this field.
  4. Setup a Selection Parameter, let it returns the data greater than ‘Z’.
  5. ‘a’ & ‘b’ will not be returned in the query result.

The ASCII of ‘a’ & ‘b’ is greater than ‘Z’, so it should be retuned, but it fails in CR. By the way, it works if you run the same queries via sqlplus.

Cause

This is the by design of CR 2008.

Resolution

Add two DB parameters NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_AI to make oracle sorting same as SQL Server.

For the detail about oracle setting please refer to the following link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch5lingsort.htm

Workaround:

  • Use command table in CR.
  • Use Store Produce in Oracle DB.
  • Use Universe as a middle layer.

Keywords

KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

Crystal Reports 2008 V1