Crystal Reports displays blank (null) values for some fields from an Oracle view

1
votes

Summary

We have a Crystal Report referencing a single Oracle view. There are no other tables or joins in the report. The view is composed of several selects, joins, and other data transformations (e.g., sum, case, to_date, group by).

Four columns from the view display as blank (null) in the report. These same columns display correctly in Oracle SQL Developer and via a select using the Oracle Data Provider for .NET in a C# application. All other columns display correctly in the report.

In addition the Browse Field option displays no values for the affected fields, but does display options for unaffected fields of the same type. Similarly, if I make an affected field a parameter of the report no options are presented.

We verified that in all cases we are connecting as the same read only user in Crystal Reports, Oracle SQL Developer, and in our Web Application.

Failed Resolution Attempts

  1. We removed and replaced the view and fields in the Crystal Report.

  2. We created a brand new report connecting to the same view.

  3. We created a new view (from the same SQL) to be used in the same report

  4. We verified database via Crystal Reports

  5. We converted implicit date conversion from

     score_month = '01-SEP-2017'

    to

     score_month = TO_DATE('01-SEP-2017', 'dd-MON-yyyy')`

    per https://stackoverflow.com/a/37729175/19977

Other Oddities

  1. Exporting the data from the view to a table works, but we want to use a view

We're at a loss as to what the cause of this issue is. Any ideas?

1
oracleviewcrystal-reports

1 Answers

0
votes

Several edits to the view were required to resolve this issue.

  1. We removed Oracle specific sql per https://stackoverflow.com/a/37729175/19977. Example:

    -- replace
TRUNC(sysdate, 'MONTH') AS score_month`
-- with
TO_DATE('01-SEP-2017', 'dd-MON-yyyy')

-- and replace
where score_month = '01-SEP-2017'
-- with
where score_month = TO_DATE('01-SEP-2017', 'dd-MON-yyyy')

  2. Fully qualify all tables and views used in the Oracle view with the schema and table name. Example:

    -- replace
FROM table t
-- with
FROM our_schema.table t

It remains unclear why the view would work as intended from Oracle SQL Developer and in application via the Oracle Data Provider for .NET but not from Crystal Reports. Further insight into this issue is welcome.