Need to Link Tables on Calculated Fields

[arachnoid]

I need to substring a field in each of my files in order to link the tables together.

Can I link the calculated fields in Crystal? If so, how?

Any information will be greatly appreciated.

For instance I would substring the following fields, then join the tables based on the substrings.

SUBSTR(JEASRDET.ASLITM,4,22) and SUBSTR(F42119.SDLITM,4,22).

 

[IanWaterman]

You can join them conventionally on other fields (if such relationships exist) then add your substr condition to the select expert.

Failing that you will need to either create a view that links the tables as you descibe or build a command to do it.

Ian

 

[AndyMc]

arachnoid,

Another possibility is to link the tables via a subreport. They allow formula links that are substrings. The link to the subreport will act like a left-outer-join.

Andy

 

[lbass]

Depending upon your version (which you should always specify), you can use a command as your datasource and in the "from" clause of your query, you can use the substrings as linking fields. Depending upon your datasource, you can either use substr() as in your first post, or use {fn substring()}.

-LB

 

[arachnoid]

Thanks to all for the responses.

I am using Crystal XI and its my understanding that I cannot modify the SQL statement. Is that not the case? If that is the case, how would I go about modifying the query itself?

Thanks again.

 

[lbass]

You can't modify it in the database->show SQL query area, but you can use a command as your datasource. Open a new report->blank->your datasource->add command and enter the query there. I often will do a dummy report to get the SQL query automatically generated and then copy the query from show SQL query into a new report->command area. Then I adadpt it from there--adding parameters, etc.

-LB