Crystal - Oracle View

[Tenloe]

I have created a view in Oracle. When I run the Crystal report I get no rows returned. If I use any columns from the non view tables I get data back, can anyone help. If I take the sql out of Crystal and run it in SQL Navigator, I get the data back just fine, it also runs fine in SQL server.

 

[Turkbear]

Hi,
Be sure the link from the non-view tables to the view is a Left-Outer one.




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

 

[Tenloe]

ok, tried left joining and unfortunately same results - now rows returned.

 

[Turkbear]

Hi,
Please describe the table and view structures, the joins ( from What to What on what field) and any selection criteria you are applying..


Thanks



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

 

[Tenloe]

I am joining the table to the view thru a claim number. If I left join and just put in the claim numbers from both tables, I get the claim table rows but not the view rows. I know the claim numbers are in there because I have checked. The view is building stauts for each claim thru periods of claim lifespan.

SELECT p.claim claim, d.dt dt, p.status currstatus, LAG (p.status, 1, NULL) OVER (PARTITION BY p.claim ORDER BY d.dt) prevmstatus, month_end, CASE WHEN d.quarter_end = 'Y' THEN LAG (p.status, 3, NULL) OVER (PARTITION BY p.claim ORDER BY d.dt) ELSE NULL END prevqstatus, quarter_end, CASE WHEN d.semi_end = 'Y' THEN LAG (p.status, 6, NULL) OVER (PARTITION BY p.claim ORDER BY d.dt) ELSE NULL END prevsstatus, semi_end, CASE WHEN d.annual_end = 'Y' THEN LAG (p.status, 12, NULL) OVER (PARTITION BY p.claim ORDER BY d.dt) ELSE NULL END prevastatus, annual_end FROM rptdate d, KEMpval p WHERE d.dt BETWEEN p.trans_dt AND NVL (p.next_trans_dt - 1, '01-JAN-2100') AND d.month_end = 'Y' AND d.dt <= TO_DATE ( '31-' || 'dec-' || TO_CHAR (ADD_MONTHS (SYSDATE, 12), 'yyyy') )