CR7 Substring in the SQL query

[monkeylizard]

I have two tables I need to link using a zip code stored in string fields. Table A has a mix of zip and zip+5 data and Table B only has the five digit zip.

I'm using a LEFT JOIN to get all of my records returned, but any in table A that have the Zip+5 return a null value for the county (stored in Table B).

If I grab the SQL query and run it in SQL QA, I get the results I want by using SUBSTRING(fieldname,1,5)

Can I get Crystal to do the SUBSTRING?

Using CR7.0 and SQL7.0

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[synapsevampire]

It would be best long term to create a view which adds another field which truncates the exisitng field using substring, then all tools going forward can use it instead.

Crystal 7 solutions aren't pretty, you can use a subreport and a formula which truncates the field to 5 characters and link on that, or you might be able to modify the sql under Database->Show SQL Query, I can't recall what CR 7 allowed for, or you might try just adding the join into the Report->Edit Selection Formula-Record, I've seen people successfully cheat it that way.

Officially, I think that Bo would state use a subreport, and of course I wouldn't consider anything but creating a View for the table to allow for like data structures.

-k

 

[monkeylizard]

Looks like the view will work. Thanks.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.