Why doesn^t this join work?

[FRED_EMMERICH_(Guest)]

I have 3 tables. I join from table A to Table B, no problem. I want to join table B to table C on another field, except the values in table C don^t correspond to a complete field in table B. e.g.____Table b field: (this is the contents of the field)____^Code: 12345^____table C field:____^12345^____I created a calculated field that extracted the code from the field in table B, but R&R won^t let me use that field in a join. How can I make this work?____

[kfleming]

In order to use a calculated field as a join field in a report, the calculated field has to be SQL engine evaluatable (since we will need to send it as part of the SELECT where clause).____When you create a calc, R&R uses the file RSWSQL.INI to determine if the expression can be sent to the engine. __If a field is engine evaluated, there will be an (x) listed on the status bar when you select that field.____For example, open the R&R sample report called Grouped Columnar Report.RSW.____Then go to the calculations menu and select the field amount.__It^s expression is QUANTITY*PRICE and on the status bar you will see the x.____What expression are you using to extract the number portion of your field? We may need to revise it to make sure it uses a function that can be translated.____Regards,__Kathleen__R&R Support

[FRED_EMMERICH_(Guest)]

I use a few of the string functions to extract a substring, pretty much like this: (total length of string is not always the same)____^Code: 12345^____I use the substring functions to extract the 12345, which is what I want to do the join on.____Thanks

[FRED_EMMERICH_(Guest)]

I use a combination of strsearch and substring. Looking at the .ini, strsearch is not going to work. My actual string looks like this:____DBSKEY=^123DFFG^ AND PROPNUM=^DFFRE^____I need the value after PROPNUM, not inluding quotes.

[kfleming]

Can you use combination of AT() and SUBST()?__That is translatable for most database platforms.____Kathleen__R&R Support

[FRED_EMMERICH_(Guest)]

I^ll try AT and SUBSTR but the length of the string I need to pull is not the same every time which cause problems.