Oracle View Missing Data

[tpeters1]

I have created a CR XI Report using an Oracle 10g View as the data source and it does not return any data. The same query using the same user and other tools like TOAD or SQL*Plus return many rows. What is going on?!?

Here is the Crystal Reports Show SQL Query:
[tt]
SELECT "TODD_TEST9_VU"."USPS_ADDRESS"
FROM "DQCO"."TODD_TEST9_VU" "TODD_TEST9_VU"
[/tt]
Here is the Oracle view creation command:
[tt]
CREATE OR REPLACE VIEW DQCO.TODD_TEST9_VU
(USPS_ADDRESS)
AS
SELECT srv.usps_address
FROM ltvsrc.ltv_servicing srv
WHERE srv.effective_dt = '31-jan-2008'
/
[/tt]
Both Crystal and the alternate query tools are running the query as the same user: DQCO. The view selects data from another schema (LTVSRC) but that should not matter because all the necessary Grants are in place and the query works on all the other tools using the DQCO user so why wouldn't it work in Crystal?

CR version 11.5.8.826
Oracle 10

 

[Turkbear]

Hi,
Just to clarify:
If you log on thru SqlPlus as DQCO and to the same database as the report is connected to and paste:

SELECT "TODD_TEST9_VU"."USPS_ADDRESS"
FROM   "DQCO"."TODD_TEST9_VU" "TODD_TEST9_VU";

you get records but in Crystal you get none?

Do you have any Group or Record selection criteria defined in the Report?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tpeters1]

Yup.

I just reconfirmed. SqlPlus returns rows. Crystal does not.

No group or record selection criteria are implemented in the the report.

*sigh*

 

[Turkbear]

Hi,
Couple of more things to test:
When the report is open in Crystal, do a database..Verify Database and see if it finds anything unmapped.

Also, in the field explorer right-click a field and try 'browse data'..Anything?

Hope it helps..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tpeters1]

Here's hoping....

Verify Database returns "Database is up to date".

Browse Data displays the column data type but no data rows.

 

[Turkbear]

Hi,
Odd...That indicates that the structure of the view is 'known', but that no data is available -- does the data type shown match the underlying table data type?

Also, can you connect the report to the table directly and not to the view ( use Change datasource location) and try the report that way?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tpeters1]

The datatype matches.

Connnecting to the source table of the view works fine. The data comes through loud and clear.

Of course view still does not work. Accessing the data has never been a problem. The problem is my seeming inability to utilize the advantages of database views.

BTW - Crystal reporting on other cross-schema views using other users and schemas works.

 

[Turkbear]

Hi,
Even odder...
One last desperate attempt :

Try creating a command object and use the generated query from the report as its code..Remap the fields to that command object's fields and delete the view from the report..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tpeters1]

Dat don work eeder.