Weird Results with "-" in Parameter Data..

[jdemmi]

I've been around Crystal for a long long time. And I've seen my quirks. But this is a new one for me and perhaps the strangest. And, to tell you the truth, it's very bothersome because it effects the integrity of the data result set.

Anywho.....

CRXI R2/Oracle

Varchar field in DB (FIELDA)

Data resembleswe'll call this TABLE1.FIELDA)
ABC-DEF-123
ABC-DEFQ-123
ABC-DEFQ-456
etc

Basic report, simple select expert

{V_PCR_PERSON_ROLE.ACR_ROL_CODE} in PARAM1 to PARAM2

Run report, enter A for PARAM1 and Z for PARAM2 -- OK, correct data returned

Run report again enter ABC-DEF-123 for both PARAM1 and PARAM2 - OK, correct data returned

Run report again enter ABC-DEFQ-456 for both PARAM1 and PARAM2 - OK, correct data returned

Run report again enter ABC-DEFQ-123 for PARAM1 and ABC-DEFQ-456m for PARAM2 - NO DATA RETURNED.

View SQL, copy paste into SQL+ -- data returned, correct result set SQL is >= param1val and <= param2val

So, go to the select expert and REPLACE the "-"'s with nothing -- like this...

replace({TABLE1.FIELDA},'-','') >= replace({?PARAM1},'-','') and
replace({TABLE1.FIELDA},'-','') <= replace({?PARAM2},'-','')

results are OK -- correct data returned.

So, I've defined at least 1 possible work around...but WTF is the problem? Has anyone ever seen this behavior before?

How did you resolve it?

-- Jason
"It's Just Ones and Zeros

 

[jdemmi]

Not even ONE response? REALLY?

-- Jason
"It's Just Ones and Zeros

 

[lbass]

I couldn't recreate your issue--using either the Xtreme database or Oracle with XI. All parm selections worked as expected.

-LB

 

[Turkbear]

Hi,
Is the m here a typo?:

ABC-DEFQ-123 for PARAM1 and ABC-DEFQ-456m





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

 

[lbass]

Jason,

I should have said Xtreme database with XI, and Oracle with X. Have you checked with SAP to see if there is a known issue for certain versions/service packs?

I had the same question as Turkbear, and I tried it with and without the m as the parameter selection, and it still worked.

-LB

 

[jdemmi]

Turk -- Yes, the m is a typo

Lbass -- odd. I've even expected spaces or other funky stuff going on but the fact that all works correct when the - is removed says otherwise. What version of Oracle, what character set and what connection method (obdc? native?) did you use?

-- Jason
"It's Just Ones and Zeros