Crystal Returning Wrong Results

[puma51]

Hi

Crystal 10 and Oracle 8

I have created a view in oracle which when queried through SQL+ or MS Access returns correct results.

When I use Crystal to connect to the view it returns completely different results.

The view has a condition that excludes records with a date range of <'01-JUN-05' but when I link through Crystal it displays records with dates prior to '01-JUN-05'

Can anyone tell me why this would happen?

Thanks

puma

 

[dgillz]

If you remove your record selection completely, does your crystal report match your view?

Also please post your record selection formula.

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

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Madawc]

Is Crystal recognising it as a date? If not, you need soemthing like Cdate({value}) to convert it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[puma51]

Hi don't actually have a record selection in Crystal I'm just looking at the basic view:-

CREATE OR REPLACE VIEW VW_MIN_ENEWS_LINK
AS
SELECT MIN(LINK_NO) AS LINKNO,
MIN(LNK_DAT) AS LINKDATE
FROM LINKS
WHERE STFDIVN_COD = 'KBM'
AND LNKOUTM_COD = 'PMARK'
AND LNKREAS_COD = 'WEBENL'
AND LNK_DAT > '01-JUN-05'
GROUP BY COMPANY_NO

Also have tried cdate conversion but no difference and have also tried changing grouping on server etc but again, no difference.
I have tried making a table from the view and using it instead and this works fine but will mean I need to run an overnight update to keep it up to date. Would much prefer to run the report straight from the view if possible.

Thanks

puma

 

[dgillz]

So the view itself is returnng the wrong data independent of crystal? Is that correct?

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

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[MJRBIM]

What happens if you change the view to a stored procedure...?

 

[puma51]

Hi

View is returning the correct results. Only when querying it through Crystal the problem happens.

Not techy enough to write stored procedures I'm afraid.

Thanks

 

[GJParker]

What is the SQL created by your crystal report ? This can be found in the Databse menu -> Show SQL Query. Please postthe SQL here.



Gary Parker
MIS Data Analyst
Manchester, England

 

[puma51]

Hi Gary, thanks


SELECT "BLK_MIN_ENEWS_LINKS"."LINKNO", "LINKS"."COMPANY_NO", "MSHMATCH"."MAILSHOT_NO", "LINKS"."LNK_DAT", "LINKS"."CONTACT_NO", "LINKS"."STAFF_NO", "LINKS"."STFDIVN_COD", "LINKS"."LNKREAS_COD", "LINKS"."LNKOUTM_COD"
FROM "LIVE"."BLK_MIN_ENEWS_LINKS" "BLK_MIN_ENEWS_LINKS", "LIVE"."LINKS" "LINKS", "LIVE"."MSHMATCH" "MSHMATCH"
WHERE ("BLK_MIN_ENEWS_LINKS"."LINKNO"="LINKS"."LINK_NO") AND ("LINKS"."CONTACT_NO"="MSHMATCH"."CONTACT_NO") AND ("LINKS"."LNK_DAT">=TO_DATE ('01-01-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "LINKS"."LNK_DAT"<TO_DATE ('31-03-2007 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) AND "MSHMATCH"."MAILSHOT_NO"=844
ORDER BY "LINKS"."CONTACT_NO", "LINKS"."COMPANY_NO"

Cheers

 

[GJParker]

According to your earlier post your view is called VW_MIN_ENEWS_LINK , but your report isn't using theis view it's using 3 tables

BLK_MIN_ENEWS_LINKS
LINKS
MSHMATCH

the view is not used at all.



Gary Parker
MIS Data Analyst
Manchester, England