Record Selection Statement!!!

[Soleil525]

I'm using CR8.5 and Oracle 8 backend
Here are my select statements:

If {?LastName} = * then
If {?First} = * then
If {ENCOUNTER_TYPE} = BV then
{VISIT_DATE} in {?V_Start} to {?V_End}
Else
UpperCase(TrimRight({LASTNAME})) = UpperCase({?LastName}) and
UpperCase(TrimRight({FIRSTNAME})) = UpperCase({?First}) and
{ENCOUNTER_TYPE} = BV and
{VISIT_DATE} in {?V_Start} to {?V_End}

When I enter * for last and first name parameters and enter a date range, the report returns six records. However, when I pick one person (I enter last and first name for the parameters...same date range) from the six records to test the Else part of the statement, the same six records return as if I enter * for the last and first name parameters.
-there are quotations around * and BV
-I have also try:
If {?LastName} like * then
If {?First} like * then...not working either
-I have also try without the TrimRight and Uppercase functions in the Else part, not working either.
Can someone please tell me what is wrong with these statements. I have spent a few hours and couldn't figure out Your help is greatly appreciated.
Bryan

 

[rhinok]

Your use of If-Then-Else statements and functions in your record selection criteria might kill performance. There are a couple of things you can do to alleviate this problem:

1) Create a SQL Expression that trims and converts your name fields to uppercase. Below are samples:

//CR ORACLE ODBC Sample:
{fn UCASE(({fn RTRIM(table."LASTNAME&quot})}

//Native Driver Sample:
UPPER(RTRIM(table."LASTNAME&quot)

2) Create edit masks for your Name parameters that automatically convert your parameter to uppercase. A sample edit mask would be >CCCCCCCCCCCCCCCCCCCC which allows up to 20 characters that will all be converted to uppercase.

3) Modify your record selection statement to use the Like clause:

Sample record selection:
{%LastName} like {?LastName} and
{%FirstName} like {?FirstName} and
ENCOUNTER_TYPE} = 'BV' and
{VISIT_DATE} in {?V_Start} to {?V_End}

Pros:

All processing will now occur server side rather than client-side. Depending on a number of factors, this may increase performance significantly.

Partial searches, such as 'JOH*' which would return 'JOHANSON' and 'JOHNSON', are enabled.

Cons:

A wildcard search (*) will not return Last Names or First Names if those values are null. Based on the type of information you're gathering, however, this shouldn't be a problem. If you do have null values in these fields you can include the IfNull(,) clause (ODBC) or the NVL(,) clause (Native) to convert Null values to a default value:

//CR ORACLE ODBC Sample:
{fn UCASE(({fn RTRIM({fn IFNULL(table."LASTNAME","NULL&quot])})}

//Native Driver Sample:
UPPER(RTRIM(NVL(table."LASTNAME","NULL&quot))

 

[Soleil525]

Thank you for the tips in regard to the performance. But I still don't understand why the Else part of my selection statement not working. I'm connect to the DB via ODBC. Do you have other suggestion?

Thanks,
Bryan

 

[rogar]

You have 3 If's and only one Else. Your Else clause is executed ONLY if the innermost If is false. Try this:

If {?LastName} = * and {?First} = * and {ENCOUNTER_TYPE} = BV then
{VISIT_DATE} in {?V_Start} to {?V_End}
Else
UpperCase(TrimRight({LASTNAME})) = UpperCase({? LastName}) and
UpperCase(TrimRight({FIRSTNAME})) = UpperCase({?First}) and
{ENCOUNTER_TYPE} = BV and
{VISIT_DATE} in {?V_Start} to {?V_End}