Joining Padded and Non Padded Fields (JD Edwards)

[manney]

I'd like to report against our company Job-tracking database and JDE OneWorld. The only commonality is our company Job Cost Number (JCN).

Our implementation of JD Edwards OneWorld uses fields which are padded with spaces; our Job Tracking system uses an unpadded field.

When the two tables are linked, Crystal Reports sees different values, and thus returns no matches (i.e., " 1234" is not equal to "1234&quot.

How do I handle this? I'd rather stay away from subreports, as performance will be seriously compromised. I can strip the spaces in MS Access and report against that, but -- again-- performance is an issue.

Running Crystal Reports 8.0, NT4 or Win 2000
Both databases are hosted by Microsoft sql server
Job-Tracking database is indexed text field, length 20
JDE is indexed, length 12

Any bright ideas?
Thanks

 

[synapsevampire]

You may be able to customize the FROM in the SQL to Join by a trimmed version of the column, this is version dependent, and you didn't share that. It's been several years since I've worked with JD Edwards.

Alternatives might be to create Views or SP's on the JD Edwards side to expose proper data.

-k

 

[manney]

Thank you for your reply. Since I'm joining 2 different databases in Crystal Reports, I cannot edit the SQL statement through Crystal.

I guess a custom view (arrrgh!) is my only solution.

Thanks again,
P Manney