use Views in Reports

[Sot]

Any one help ...
If i run the following query in sqlserver query analyser i am getting output....But if i paste the same query in Crystal Reports -Show query option and save the report and run the report it si not giving proper output...I meam
first two fields is not there?(patient_age_vw."age_group",patient_age_vw."patients" )

here first query is

SELECT
patient_age_vw."age_group",patient_age_vw."patients" ,patient_age_vw_male."count",patient_age_vw_female."count",patient_age_vw_other."count"
FROM
{ oj "patient_age_vw" patient_age_vw right OUTER JOIN "patient_age_vw_male" patient_age_vw_male ON patient_age_vw."sequence" = patient_age_vw_male."GROUP"
right OUTER JOIN "patient_age_vw_female" patient_age_vw_female ON patient_age_vw."sequence" = patient_age_vw_female."GROUP"
right OUTER JOIN "patient_age_vw_other" patient_age_vw_other ON patient_age_vw."sequence" = patient_age_vw_other."GROUP"
}


the second query which i am seeing in show query option...If i paste the above query and save then it is show following...
SELECT
patient_age_vw_female."Count",
patient_age_vw_male."Count",
patient_age_vw_other."Count"
FROM
{ oj "patient_age_vw" patient_age_vw right OUTER JOIN "patient_age_vw_male" patient_age_vw_male
ON patient_age_vw."sequence" = patient_age_vw_male."GROUP" right OUTER JOIN "patient_age_vw_female" patient_age_vw_female
ON patient_age_vw."sequence" = patient_age_vw_female."GROUP" right OUTER JOIN "patient_age_vw_other" patient_age_vw_other
ON patient_age_vw."sequence" = patient_age_vw_other."GROUP" }

Kumar

 

[synapsevampire]

I assume that you're using CR 9.

Try creating a view or SP from the sql, will Crystal use that correctly?

Create View TestView as
SELECT patient_age_vw."age_group", patient_age_vw."patients" , patient_age_vw_male."count", patient_age_vw_female."count", patient_age_vw_other."count"
FROM
{ oj "patient_age_vw" patient_age_vw right OUTER JOIN "patient_age_vw_male" patient_age_vw_male ON patient_age_vw."sequence" = patient_age_vw_male."GROUP"
right OUTER JOIN "patient_age_vw_female" patient_age_vw_female ON patient_age_vw."sequence" = patient_age_vw_female."GROUP"
right OUTER JOIN "patient_age_vw_other" patient_age_vw_other ON patient_age_vw."sequence" = patient_age_vw_other."GROUP"
}

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Sot]

Yes It is working...I f i create another view....and call in my report....Thanks for ur great help....
Kumar