Help with custom table joins

[GaryCWL]

I know in Crystal that I can perform the following joins:
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Outer Join

Is it possible to perform a custom join similar to the following SQL join?

SELECT *
FROM CO_DOC
JOIN Market_Type
ON LEFT(CO_DOC.PROJECT,8)=LEFT(Market_Type.Account,8))

Instead of joining the entire Project Name to the Account Name, I want Crystal to compare only the first 8 characters and treat those as equal if the first 8 characters are in fact the same.

For example, there are many instances where the Project Name could be "ABC Circuit - Nevada" and Account Name may just be "ABC Circuit". In this case, I want all version of ABC Circuit to fall within the same project whether it's in Nevada, California, Arizona, etc...

Thanks,
Gary

 

[synapsevampire]

Depends on your software version, please remember to post basic information.

In CR 9 and above, select the Add Command shown directly underneath your connection and you can paste in REAL SQL.

-k

 

[GaryCWL]

Thank you synapsevampire. That worked.

Sorry I forgot to mention that I'm using Crystal Reports XI.