Record Selection puzzle

[atthevilla]

Crystal 10, Oracle 10g.

My record selection is producing 0 records:

{ADDRESS.REGION} = "New York City"
0 records selected

When I change the record selection to this, I get correct results:
trim({ADDRESS.REGION}) = "New York City"
36,738 records selected

Or to this:
{ADDRESS.REGION} startswith "New York City"
36,738 records selected


I created this boolean formula which evaluates to TRUE for the pertinent records but this same statement is not producing any records when used in the record selection.
{ADDRESS.REGION} = "New York City"

I tried using the Select Expert also, selecting values from the dropdown list. I can use the trim or startswith solution but I fear I’m missing something here. Anyone seen this before?

 

[Turkbear]

Hi,
It appears that the {ADDRESS.REGION} field in the database has trailing spaces - That would explain that behavior.

If you have direct access ( or know the DBA ) you can eliminate the issue by executing an Update statement against the data : Set {ADDRESS.REGION} = Trim({ADDRESS.REGION})








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[atthevilla]

Yes the field in question is padded but I found the reason for the inconsistency with different fields. For example, {BIO.CONSTITUENT_VALUE} = "Alumni" selects the correct number of records without trimming. The field that succeeds in record selection is a CHAR field, the one that fails is a VARCHAR2 that needs the trim. This is an Oracle issue. Thanks for your help.

 

[Turkbear]

Hi,
Odd, since CHARs are blank-padded in Oracle and VARCHAR2s are not...:

http://www.orafaq.com/faq/what_is_the_difference_between_varchar_varchar2_and_char_data_types

and

http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10807/b_char.htm

Could it be that whatever enters the data is adding the blanks?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[atthevilla]

Yes, I'm reporting on a materialized view and it's padded all the VARCHAR2 values in this field out to 65. I think I need to take this up with the IT Dept. Sigh.

 

[Turkbear]

Hi,
When speaking to them, thank them for disabling one of Oracle's better features - the non-padding VARCHAR2 type.





To Paraphrase:"The Help you get is proportional to the Help you give.."