Startswith function returns no data

[Cavitt]

CR Version: Crystal Reports XI Professional SP2
Database: Oracle 10.2
Connection: Native Oracle Driver

Problem:
I'm attempting to select only data that starts with a string provided by a parameter(Or All). The "All" function works fine, but when I enter a value, like "A", no records are returned.
I've verified that the parameter and field data types are both strings, and that data exists that matches the criteria I'm entering into the parameter. I've even tried hard coding a valid value into the formula.

This database will get quite large and I have several parameters like this to setup for this report, so it's important that I get as much stuff to process at the database level as possible...

I've tried the following:

(
{PATIENT.PAT_NAME} startswith {?Patients Name} or
{?Patients Name} = "All"
)

This passes to the database, it just doesn't return data.


Next, I tried:

(
totext ({PATIENT.PAT_NAME}) startswith {?Patients Name} or
{?Patients Name} = "All"
)

This works (returns the correct data) however, it does not pass to the database.

Since that returned data, I've tried creating a SQL expression to convert the string in an attempt to get it to pass the select statement to the database.

SQL Expression Field:
(TO_CHAR ("PATIENT"."PAT_NAME" )

Select Statement:
(
{%PatientName} startswith {?Patients Name} or
{?Patients Name} = "All"
)

Again, this passes to the database, but does not return data.


Any Ideas?

 

[Turkbear]

Hi,
Try:

If
({?Patients Name} = "All")
Then
True
Else
If
({?Patients Name} <> "All")
Then
{PATIENT.PAT_NAME} startswith {?Patients Name}






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

 

[Cavitt]

I get the same result as my first attempt. No data, but it passes to the database.

I suspect this has something to do with Oracle, but I'm not familiar enough with it to know why... It's like it does not recognize the field as a string or something weird like that.

Thanks for the quick response though...

-cd

 

[Turkbear]

Hi,
Please post the Sql created by CR ...

The Field Explorer should show you the datatypes..

Connect to Oracle directly, using SqlPlus, and do a desc on the table then try a simple query using the 'Like' operator with the trailing % wildcard..





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

 

[Cavitt]

Unbelievable... The field is case sensitive... I’ve never encounter that, but I’m more accustom to working with SQL Server.


Thanks for the help anyway.

-cd

 

[Cavitt]

For those who possibly run into this problem, my solution ended up as:

Select Formula:
(
({PATIENT.PAT_NAME}) startswith uppercase ({?Patients Name}) or
{?Patients Name} = "All"
)

SQL Passed: When I enter “b” in the parameter

WHERE "PATIENT"."PAT_NAME" LIKE 'B%'



This accopmlishes the task in the least amount of code...


-cd