Mantissa
asked on
Trimming Data in Crystal
I wish to retrieve data from a particular database field via Crystal, only I want to trim the data that comes in, and report against this data.
Ex. I am pulling in data from a field called REFERENCE, and it gives me a value of 30101301. I only want to display and work with the first three numbers here: '301'. I also want to do a lookup using this number, to another table that provides a correlating description for what 301 is.
Anyone know the SQL Statement or steps in Crystal to do this?
Thanks.
Ex. I am pulling in data from a field called REFERENCE, and it gives me a value of 30101301. I only want to display and work with the first three numbers here: '301'. I also want to do a lookup using this number, to another table that provides a correlating description for what 301 is.
Anyone know the SQL Statement or steps in Crystal to do this?
Thanks.
ASKER
Hi,
I must use Crystal Reports to get this.
I can't build a query or procedure in the database.
I am working with an Oracle 8.0.5 instance.
Thanks!
I must use Crystal Reports to get this.
I can't build a query or procedure in the database.
I am working with an Oracle 8.0.5 instance.
Thanks!
One way is to use a Crystal Formula Field and the Left() Function:
Left(REFERENCE, 3)
Left(REFERENCE, 3)
ASKER
dbirdman:
That sounds like what I need....but unfortunately I am not really a 'power user' of Crystal, and need a bit of help integrating that formula.
I tried making a formula field and using what you specified above, but the editor kept giving me errors (saying a ')' is expected here).
Could you possibly give me the steps to apply this properly? Also, do I need to speficy the table name in there somewhere? ie. Left(TABLENAME.REFERENCE, 3)
Thanks for the help, looks like it is almost there!
That sounds like what I need....but unfortunately I am not really a 'power user' of Crystal, and need a bit of help integrating that formula.
I tried making a formula field and using what you specified above, but the editor kept giving me errors (saying a ')' is expected here).
Could you possibly give me the steps to apply this properly? Also, do I need to speficy the table name in there somewhere? ie. Left(TABLENAME.REFERENCE, 3)
Thanks for the help, looks like it is almost there!
ASKER
Aha....got it.
I changed the formula to this:
Left({TABLENAME.REFERENCE} , 3)
The additional brackets did the trick! I just need to see if I can pull the description in from another table....
I changed the formula to this:
Left({TABLENAME.REFERENCE}
The additional brackets did the trick! I just need to see if I can pull the description in from another table....
ASKER
Ok, this one is giving me a headache now...
I named the above formula @trim, and now I want to select a description field in a different table where the description's associated VALUE field = @trim
The sloppy SQL statement I am trying to execute is as follows:
select TABLENAME2.DESCRIPTION from TABLENAME2 where TABLENAME2.VALUE = {@trim}
I hope that isn't too confusing? Basically there are a set of values in TABLENAME2 that have associated DESCRIPTIONS, that I want to correspond to my @trim formula.
Thoughts? Thanks for the help so far!
I named the above formula @trim, and now I want to select a description field in a different table where the description's associated VALUE field = @trim
The sloppy SQL statement I am trying to execute is as follows:
select TABLENAME2.DESCRIPTION from TABLENAME2 where TABLENAME2.VALUE = {@trim}
I hope that isn't too confusing? Basically there are a set of values in TABLENAME2 that have associated DESCRIPTIONS, that I want to correspond to my @trim formula.
Thoughts? Thanks for the help so far!
Try something more like this
SELECT table2.Description
FROM
table1 LEFT JOIN table2 ON
left(Refernce,3) = table2.value
You have to get table1 involved to get the original value to trim.
good luck
mlmcc
SELECT table2.Description
FROM
table1 LEFT JOIN table2 ON
left(Refernce,3) = table2.value
You have to get table1 involved to get the original value to trim.
good luck
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have been unable to get this to work in Crystal despite a lot of work. I will spend some more time on it Monday and let everyone know how it goes.
Thanks!
Thanks!
Need any more help?
mlmcc
mlmcc
ASKER
Hi mlmcc....sorry I've been busy! :S
I have someone else working on getting this to work, but they are basing the report on information gathered at the link you provided above...as well as your proposed join statement. Thus, I will award the answer to you, and adjust the grade to a "B" to compensate.
Thanks to you and dbirdman for the great tips...I'll come back soon with more Crystal Questions!
I have someone else working on getting this to work, but they are basing the report on information gathered at the link you provided above...as well as your proposed join statement. Thus, I will award the answer to you, and adjust the grade to a "B" to compensate.
Thanks to you and dbirdman for the great tips...I'll come back soon with more Crystal Questions!
ASKER
Put me on the right path...thanks!
Glad to help
mlmcc
mlmcc
If you can build in the database, what database are you using?
mlmcc