Data Missing from Crystal report when using a materialized view

[pavandyke]

We are using Crystal Reports 10 with an Oracle 10 database. Some of the reports have used the Visual Linking expert to join two tables together. One of these tables is actually a materialized view. For the most part this seems to be working. However we recently ran into a situation where the report was not returning any data for one particular set of parameters (this is the only reported issue, there could be more). I could take the SQL query that is generated from Show SQL Query option and run it in SQL Navigator and it returned the one row that was expected. Even more perplexing I created a new report using a Command object and pasted the same SQL in it and it returned the one row expected. Has anyone run into similar issues using materialized views? I don't really understand how they work. Should all our reports (about 10 of them) that use this materialized view be changed to use a command object? If so I guess the more basic question is why does using a command object seem to work and just letting crystal create the SQL doesn't.

 

[Telemachus]

Hi pavandyke,

I have not run into an issue like this but one possibility that i could think of is that the database needs to be verified from crystal. Try verify database from crystal and run the report.
Materialised views get refreshed from time to time so that may not be reflecting in the report.

Thanks,
Madhu

 

[dgillz]

Please post the record and group selection formulas, and the show SQL query. Often times the record or group selection formula does not pass thru to the SQL query, and when you are experiencing may be expected behavior.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[pavandyke]

Record Selection

(
(isnull({GES_ROL_WEB_RPT_SET.CNTRCT_ID})
AND isnull({GES_ROL_WEB_RPT_SET.ADMIN_UNIT_ID})
) OR
(not isnull({GES_ROL_WEB_RPT_SET.CNTRCT_ID})
AND isnull({GES_ROL_WEB_RPT_SET.ADMIN_UNIT_ID})
AND {@vw_cntrct_id} = totext({GES_ROL_WEB_RPT_SET.CNTRCT_ID})
) OR
(not isnull({GES_ROL_WEB_RPT_SET.CNTRCT_ID})
AND not isnull({GES_ROL_WEB_RPT_SET.ADMIN_UNIT_ID})
AND {@vw_admin_unit_id} = {GES_ROL_WEB_RPT_SET.ADMIN_UNIT_ID}
AND {@vw_cntrct_id} = totext({GES_ROL_WEB_RPT_SET.CNTRCT_ID})
) OR
(isnull({GES_ROL_WEB_RPT_SET.CNTRCT_ID})
AND not isnull({GES_ROL_WEB_RPT_SET.ADMIN_UNIT_ID}) and
{@vw_admin_unit_id} = {GES_ROL_WEB_RPT_SET.ADMIN_UNIT_ID}
)
) and
{GES_ROL_WEB_RPT_SET.WEB_NM} = {?WebName} and
{GCR_LIFE_RPT_MVW.LIFE_WAIVER_CD} = "WVR"

Group Selection is blank

Show SQL query

SELECT "GCR_LIFE_RPT_MVW"."TRACKING_CD",
"GCR_LIFE_RPT_MVW"."APPROVED_BNFT_AMT",
"GCR_LIFE_RPT_MVW"."CLAIM_EVENT_NBR",
"GCR_LIFE_RPT_MVW"."CM_STATUS",
"GCR_LIFE_RPT_MVW"."CM_DATE_OF_BIRTH",
"GCR_LIFE_RPT_MVW"."LAST_OR_ORG_NM",
"GCR_LIFE_RPT_MVW"."CM_POLICY_NO",
"GCR_LIFE_RPT_MVW"."EVENT_STATUS_CD",
"GCR_LIFE_RPT_MVW"."EVENT_TYPE_CD",
"GCR_LIFE_RPT_MVW"."CM_INCURR_DATE",
"GCR_LIFE_RPT_MVW"."MEM_CEASED_WORK_DT",
"GCR_LIFE_RPT_MVW"."MEM_DEATH_DT",
"GCR_LIFE_RPT_MVW"."CM_MEMBER_NM",
"GCR_LIFE_RPT_MVW"."CM_MEMBER_SSN",
"GCR_LIFE_RPT_MVW"."PRODUCT_ID",
"GCR_LIFE_RPT_MVW"."CM_RECVD_DATE",
"GES_ROL_WEB_RPT_SET"."WEB_NM",
"GES_ROL_WEB_RPT_SET"."CNTRCT_ID",
"GES_ROL_WEB_RPT_SET"."ADMIN_UNIT_ID",
"GCR_LIFE_RPT_MVW"."CM_APPROVED_DATE",
"GCR_LIFE_RPT_MVW"."LIFE_WAIVER_CD"
FROM "GRP"."GCR_LIFE_RPT_MVW" "GCR_LIFE_RPT_MVW",
"GRP"."GES_ROL_WEB_RPT_SET" "GES_ROL_WEB_RPT_SET"
WHERE (("GCR_LIFE_RPT_MVW"."CO_ID"="GES_ROL_WEB_RPT_SET"."CO_ID") AND ("GCR_LIFE_RPT_MVW"."GROUP_ID"=
"GES_ROL_WEB_RPT_SET"."GROUP_ID")) AND ("GES_ROL_WEB_RPT_SET"."CNTRCT_ID" IS NULL AND "GES_ROL_WEB_RPT_SET"."ADMIN_UNIT_ID" IS NULL OR "GES_ROL_WEB_RPT_SET"."CNTRCT_ID" IS NOT NULL AND "GES_ROL_WEB_RPT_SET"."ADMIN_UNIT_ID" IS NULL OR "GES_ROL_WEB_RPT_SET"."CNTRCT_ID" IS NOT NULL AND "GES_ROL_WEB_RPT_SET"."ADMIN_UNIT_ID" IS NOT NULL AND "GCR_LIFE_RPT_MVW"."TRACKING_CD"=
"GES_ROL_WEB_RPT_SET"."ADMIN_UNIT_ID" OR "GES_ROL_WEB_RPT_SET"."CNTRCT_ID" IS NULL AND "GES_ROL_WEB_RPT_SET"."ADMIN_UNIT_ID" IS NOT NULL AND "GCR_LIFE_RPT_MVW"."TRACKING_CD"=
"GES_ROL_WEB_RPT_SET"."ADMIN_UNIT_ID") AND "GES_ROL_WEB_RPT_SET"."WEB_NM"='fhsweb' AND "GCR_LIFE_RPT_MVW"."LIFE_WAIVER_CD"='WVR'

 

[RustyAfro]

The problem is going to be somewhere in your formula filters.

All the filters that start with "@" (ie: {@vw_cntrct_id}=) are evaluated after the query has run, which means local on the machine. The SQL generated will not include "@" formula filters. The one row is getting filtered from one or more of those local filters.

So it would make sense that the the "Show SQL" you are returning runs on the server and returns the row you want but not when you run the report in Crystal.

 

[pavandyke]

It seems like if that were the case then at the bottom of my preview window it would show 0 of 1 records processed. It just shows 0 records like it never retrieved any records from the query that was processed against the database.

 

[RustyAfro]

Humor me. Try commenting out all the @ filters and let us know what happens.

 

[pavandyke]

I stand corrected. Thanks RustyAfro. It turns out that I had omitted those portions of the query from the version that was "working" so I was seeing data. It turns out the problem was actually a bad data issue. They have corrected the data and the original report is now returning the desired data.

Thanks to everyone for your helpful insights.

 

[RustyAfro]

No problem ;-)