- Crystal Report(CR) 2008 query can't return the correct result against Oracle 10g.
- The comparison of ASCII does't work.
- Operation System(OS): Windows 2003 server SP2
- CR: 2008 SP1
- Database(DB): Oracle 10g
Reproducing the Issue
- Create table with a nvarchar field under Oracle 10g.
- Fill the field with three values, i.e ‘a’, ‘b’ & ‘Z’.
- Setup a CR report against this field.
- Setup a Selection Parameter, let it returns the data greater than ‘Z’.
- ‘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.
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.
- Use command table in CR.
- Use Store Produce in Oracle DB.
- Use Universe as a middle layer.
KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem