Linking tables substring possible?

[Former Member]

We would like to link an order # which is 8 characters with the 'original order number' which is also 8 but unfortunately is stored in a 24 char column and you can get it this way> ReplicateString ("0",8-LENGTH(TRIM(MID({OEORH41.OHPO#},1,8))) )&TRIM(MID({OEORH41.OHPO#},1,8))

On the database expert linking is there any way to provide this trim function so we can link correctly?

[Former Member · Accepted solution]

Hi Paul,

Your have below listed solutions options for your problem,

1. You can create a SQL Query for retrieving your report data and then use it with ADD Command option as a data source for your report.

2. In case you do not want a single query joining all your data table for retrieving report source data, You can write SQL Query only for the table which stores Order Number in 24 Char format(include substring version of order number in the select column list) and then use this command in linking tab to join with Order# using normal link joins.

Please try these options and see if this solves your problem.

--

Regards

Himanshu

[Former Member · Accepted solution]

Hi,

I do agree with Aggarwal's approach.

-Uday.

[Former Member · Accepted solution]

OK so make a command to get the order numbers and the col there with the codes we need.

this make it available to program. Sounds good i will try.

[DellSC · Accepted solution]

Be aware that if you mix one or more commands with tables in your report, it can slow your report down considerably.  This is because Crystal can't send all of the SQL to the database for processing.  Instead, it pulls data into memory and does the join there (and possibly the filtering as well).  Depending on the number rows that are returned prior to the join, this can be a very slow process.  You are MUCH better using a single command to pull ALL of the required data for the report.

-Dell