Can't link to string because of spaces
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21562
Printed Date: 01 May 2024 at 4:36pm
Topic: Can't link to string because of spaces
Posted By: carstowal
Subject: Can't link to string because of spaces
Date Posted: 23 Jun 2015 at 8:16am
I have an excel spreadsheet with a list of part numbers (text) and list of codes (text).
The codes are a 2 digit number for example: 13.
The problem is in the Oracle database (JDEdwards) the Code field is a 24 character string field, stored as 10-spaces followed by the 2-digit code, for example: " 13" with no trailing spaces.
I know I can enter the 10 space prefix in the excel file but I want to make it easy on other users to just load part numbers and codes into the excel file and run their crystal report without making them add blank spaces.
|
Replies:
Posted By: kevlray
Date Posted: 23 Jun 2015 at 10:28am
|
In a formula you could either do a LTrim({FieldName}), or a Right({FieldName},2). There is another way in which you treat the string as an array.
|
Posted By: carstowal
Date Posted: 23 Jun 2015 at 10:32am
I did write numerous formulas using trim({field name}). That works, I just was looking for a way to link the fields.
|
Posted By: DBlank
Date Posted: 24 Jun 2015 at 3:53am
|
use a command object to trim the field at the source then join to that trimmed field?
|
Posted By: kevlray
Date Posted: 24 Jun 2015 at 4:56am
I believe there is a LTrim for Oracle. Thus when joining tables it would be something like this
Inner Join sometable on LTrim(sometable.field) = othertable.field
I am not sure what other string functions are available in Oracle.
|
|