Parameter Problem with Oracle Database

[ynott]

In the select expert, I'm using the following formula:

if length {?EmployeeNumber} = 4 then {UNITS.EMPLOYEE_ID} like
{?EmployeeNumber}

So, what I'm tring to do is use select expert to eliminate some data (BTW, the formula is more complicated than this, but I narrowed the problem to this area). However, I'm having a problem. Although {UNITS.EMPLOYEE_ID} is a string value, Crystal Reports is reporting an error with the above line. It wants to add a then after if length. I even put a totext({?EmployeeNumber}), but it still doesn't like it and says (A number, currency, amount, boolean, date, time, date-time, or string is required here and automatically inserts the cursor before the {?EmployeeNumber} .

Is there a problem with the Oracle translation? Am I missing something?

 

[MalcolmW]

What are you trying to do?
if length {?EmployeeNumber} = 4 then {UNITS.EMPLOYEE_ID} like {?EmployeeNumber}
is logically equivalent to
if length {?EmployeeNumber} = 4 then {UNITS.EMPLOYEE_ID} = {?EmployeeNumber} else false
Is that what you want? Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[ynott]

Ok, this is what I'm trying to do. I wanted to keep it short so it wasn't confusing, but here it goes.

Our employees are listed in a table for every incident they are involved in. The field that I'm trying to extract from {UNITS.EMPLOYEE_ID} works like this. This is a five character area in which the first one or two characters are the area worked for the day (i.e. B1, C4, G, C). The last three characters are the employees ID number. Thus, on day one, employee number 099 might look like B1099, one day two C099, etc.

As you can tell, the database sucks. So, this is what I'm trying to do. When a supervisor runs a monthly report, he can't do *111 to get all of 111's activity (B1113 would also return with a*). Thus, the supervisor has to use ?099 and ??099 to get back all activity. Thus, two reports for every employee. The following select expert statement would solve the problem:

if length {?EmployeeNumber} = 4 and {?EmployeeNumber}[1] = "?" then ({UNITS.EMPLOYEE_ID} like
{?EmployeeNumber}) or
{UNITS.EMPLOYEE_ID}like "?" & {?EmployeeNumber} else {UNITS.EMPLOYEE_ID}like {?EmployeeNumber}

Thus, if there are 4 characters in the parameter {?EmployeeNumber} and the first character is ?, this formula will post ?099 and ??099. Therefore, there would only be a need for one report.

Thanks for your input

 

[MalcolmW]

I'm a big fan of using parentheses - is this what you are after? And note that the length() function does need parentheses.

if (length({?EmployeeNumber}) = 4 and {?EmployeeNumber}[1] = "?&quot then
(
({UNITS.EMPLOYEE_ID} like {?EmployeeNumber})
or
({UNITS.EMPLOYEE_ID}like "?" & {?EmployeeNumber})
)
else
{UNITS.EMPLOYEE_ID}like {?EmployeeNumber} Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[ynott]

Thanks for the reply Malcolm. I've already tried with and without paranthesis all over the place. The problem still boils down to the one line by itself:

if length {?EmployeeNumber} = 4 then {UNITS.EMPLOYEE_ID} like
{?EmployeeNumber}

I think it's an Oracle thing. I created an Access document with similar fields to Oracles and the formula for MS Access works and the Oracle DB does not. I know this isn't the best test since ODBC results will vary, but I've tried almost everything

 

[MalcolmW]

In that one line, the syntax for the length function is wrong. It is not just a question of style preference. The syntax is Length(<<string>>)
There may be something else wrong as well, but this syntax error alone would cause this formula not to work.
If Length({?EmployeeNumber}) = 4 Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?