Crystal Report Err

[bk_yogesh97]

I am using frontend as visual basic 6.0 and back end as oracle 9i.
Query is written for crystal report 8.5.
In query select fields i used function to retrview data and grouping that fields.
In Oracle Viewer Showing data is not same as Crystal report Viewer showing data.
It is unexpected.
So Anyone can give me best soloution.

[QVeen72]

Hi,

Can u post what the query is in Oracle.. and what Grouping u have done in CR...? Table /field names..? Grouping should be done in the same order of fields both sides, then u get perfect results...

REgards
Veena

[bk_yogesh97]

I am using frontend as visual basic 6.0 and back end as oracle 9i. Query is written for crystal report 8.5. In query select fields i used function to retrview data and grouping that fields. In Oracle Viewer Showing data is not same as Crystal report Viewer showing data. It is unexpected. So Anyone can give me best soloution.

Query:
select getdeptname(SERVICECD) AS SERVICE,NVL(getpersonname(rcd),'TOTAL') AS person,
sum(iiif('''' || v || '''' , '=', '''' ||'N' || '''', iiif('''' || D.sex || '''', '=', '''' || 'M' || '''','1','0'),'0'))as newmale ,
sum(iiif('''' || v || '''' , '=', '''' ||'N' || '''', iiif('''' || D.sex || '''', '=', '''' || 'F' || '''','1','0'),'0') )as newfemale ,
sum(iiif('''' || v || '''' , '=', '''' ||'R' || '''', iiif('''' || D.sex || '''', '=', '''' || 'M' || '''','1','0'),'0') )as revisitmale ,
sum(iiif('''' || v || '''' , '=', '''' ||'R' || '''', iiif('''' || D.sex || '''', '=', '''' || 'F' || '''','1','0'),'0') )as revisitfemale ,
COUNT(D.sex) as total
from O c,p d
Where C.ID = d.ID
and Flag = 'C'
and to_date(Date) between
to_Date(? ,'DD-MM-YYYY') and
to_Date(?,'DD-MM-YYYY')
group by getdeptname(SERVICECD),ROLLUP(getpersonname(rcd))

[bk_yogesh97]

[QUOTE=bk_yogesh97;426285]Query:
select getdeptname(SERVICECD) AS SERVICE,NVL(getpersonname(rcd),'TOTAL') AS person,
sum(iiif('''' || v || '''' , '=', '''' ||'N' || '''', iiif('''' || D.sex || '''', '=', '''' || 'M' || '''','1','0'),'0'))as newmale ,
sum(iiif('''' || v || '''' , '=', '''' ||'N' || '''', iiif('''' || D.sex || '''', '=', '''' || 'F' || '''','1','0'),'0') )as newfemale ,
sum(iiif('''' || v || '''' , '=', '''' ||'R' || '''', iiif('''' || D.sex || '''', '=', '''' || 'M' || '''','1','0'),'0') )as revisitmale ,
sum(iiif('''' || v || '''' , '=', '''' ||'R' || '''', iiif('''' || D.sex || '''', '=', '''' || 'F' || '''','1','0'),'0') )as revisitfemale ,
COUNT(D.sex) as total
from O c,p d
Where C.ID = d.ID
and Flag = 'C'
and to_date(Date) between
to_Date(? ,'DD-MM-YYYY') and
to_Date(?,'DD-MM-YYYY')
group by getdeptname(SERVICECD),ROLLUP(getpersonname(rcd))

This is actually a view which i have craeted in oracle and i have added this view in my crystal report.
All the required grouping have been done in the above view itself and nothing has been done in the crystal report.
I have just added the view to crystal Rep.
Another thing, for knowing the discrepencies between the backend and frontend, I have loaded the result of the above view in a Grid to whether its a VB6 err or CR err. And i found result is exacltly the same as of Oracle,but when i load this view in CR,it shows me some discrepencies while doing sum operations, ie all the above sum Operation.