Inlude a formula in sql command

[nilsbinnemans]

Hi,

Using Crystal report 2011
I need to link two views by one field. The field type however is different in both views, so I am not able to link them.

I have VWSCHEDULEVOLUNTEER.VOLUNTEER_KEY with numbers from 1 to ...
I have VWSRVOLUNTEERDEMOGRAPHICS with strings like VRN-1 to VRN-...

I never used sql commands as I don't really know how to write them. But would it be possible adding below formula

VRN = "VRN-"& cstr({VWSCHEDULEVOLUNTEERSTUDY.VOLUNTEER_KEY},0,"","")

to below command so that I could link these fields?

SELECT
"VWSCHEDULEVOLUNTEERSTUDY"."VOLUNTEER_KEY"

FROM
"LABPAS"."VWSCHEDULEVOLUNTEERSTUDY"

Thanks,
Nils

 

[PeterDimitrov]

You can create another view in the database and join the existing views. In SQLServer the new view will look like this:

SELECT ....
FROM view1 v1
INNER JOIN view2 v2 ON v1.Key = 'VRN-' + CONVERT(varchar(100),v2.Key)

If you do not have permissions to create a view inside the database you can create a command using the same SQL and base your report on it.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.