This Array Must Be Subscripted

[Soleil525]

Here is the problem:

My record selection statement is as follow:
Uppercase({Employer.Name}) like Uppercase({?Empl})

When I set {?Empl} parameter to accept multiple value, a record selection formula editor Window opens then display this message: This Array must be a Subscripted. For example: Array

Why do I get this message?

Your help is greatly appreciated.

 

[AndrewBaines]

If you have multiple parameters, it will return an array. You can't uppercase a whole array. Use a parameter mask to force the parameter entry to upper case instead, then you won't need to worry about the uppercase function. The report will run a lot faster too.

 

[Ngolem]

no...I think the uppercase is ok....I think the problem is with the use of "Like"

I think this applies to a one-on-one comparison....and a multiple input is an array of values...so Crystal sees an array on the right hand side and expects to see an array on the left hand side as well.

Do you really need "like" ...your parameter inputs are multi valued with wildcards in them??? I don't think so.

try this instead:

Uppercase({Employer.Name}) in Uppercase({?Empl})



Jim
JimBroadbent@Hotmail.com

 

[Soleil525]

Thank you all for your response.

Jim,
I use "Like" to give the user more flexibility. For example, the user can select to see all employers, one employer, or maybe two to three employers...Is this not allow in CR?

Andrew,
I'll try your suggestion, but I think I still have to convert the data(employer name) in the database to uppercase right?

 

[Ngolem]

What are you listing as the the optional multi-parameter inputs??

Your problem is the use of "like" in my opinion. Inorder to you it with an array such as a multiple input parameter then you would have to cycle through each element of that array...for "All Employers" it sounds as though you are using a wildcard like "*". That would not be pushed down to the server for sure

It is the "ALL Employers" that is the stumbling block

How about trying this....in your record select

if {?Empl} = "ALL" then
{Employer.Name} like "*"
else
{Employer.Name} in {?Empl}

The Uppercase doesn't seem to mean much anymore...Crystal seems to be case-insensitive for comparisons

Just instruct your user in the description to enter "ALL" for all employers or specify individual employer names.


Jim
JimBroadbent@Hotmail.com

 

[Soleil525]

Ngolem,
Thank you for your help. I'll construct the select statement as you suggested.

 

[AndrewBaines]

Crystal is as case sensitive as the database - depends upon your DB setup.
You don't need to change the DB to uppercase, just make the SQL convert it for you.

 

[Soleil525]

Andrew,

Since the Employer table has like 800,000 records I will not give the user an option to select all employers. So I have to allow the user to select one or more specific employers.

I think you are right about Crystal is being case sensitive. Here is my select statement

{Employer.Name} In {?Empl}

Let say an employer was entered into the database in all cap (BLUE CROSS BLUE SHIELD)

when I fill the parameter with a value "Blue Cross Blue Shield", the report returns no record. However, when I enter the value in all cap, the report returns the expected records. How do I ensure that SQL convert the case? My DB is Oracle, I am using CR8.5, and connect to DB via ODBC.

I can not apply the UPPERCASE function to the parameter since it was set to accept multiple values. I received an Array Error message. You mentioned use parameter mask, can you please be more specific, what character should I use. I look at the characters you can enter in the Edit Mask area and don't see one to convert the values to uppercase.
TIA

 

[AndrewBaines]

Here we go -
In the parameter, create the mask >CCCCCCCCCCCCCCCCCCCCC
with as many capital Cs as the maximum length you'll need
Create a SQL Function called 'Fred':
{fn UCASE(Customer.`Customer Name`)}

Then in the select expert, set them equal to each other:
{%Fred} = {?Customer}

This gives you a SQL Query of:
SELECT
Customer.`Customer Name`, Customer.`Last Year's Sales`,
{fn UCASE(Customer.`Customer Name`)}
FROM
`Customer` Customer
WHERE
(({fn UCASE(Customer.`Customer Name`)}) = 'ALLEY CAT CYCLES' OR
({fn UCASE(Customer.`Customer Name`)}) = 'AB')
or similar

You've lost the 'like', I can't find a way round that without using CR9. Andrew Baines
Chase International

 

[Soleil525]

Andrew,

Here is what I discover: If I write the following statements, everything seem to work fine (case-insensitive)

If {?Employer Name} = '*' then
{Employer.Name} like {?Employer Name}
Else
{Employer.Name} = {?Employer Name}

I can enter * for all employers, one or more employers regardless of case (first letter cap the rest lowercase, all cap, or all lower) the report returns the expected result??? Since I use If...Else I don't think it will push down to the server when selecting the records and I am not fully understand why these statements work, so I'll try your method. If you or anyone can figure out why these statements work the way they are, please let me know.

Thanks

 

[Ngolem]

Andrew

A question about the Case Sensitivity:

Are you saying that this is a function of the database itself and how it is set up? Or is it an option selected from Crystal?

I have tried it on some sample reports here and found that there is no case sensitivity at all...?? Jim
JimBroadbent@Hotmail.com

 

[AndrewBaines]

From the Crystal Database options tab, help text:
Case-Insensitive SQL Data
Select this check box if you want to search for strings in your SQL data without checking the case. For example, if your report contains SQL data and the data is of mixed case (red, RED, Red), a case-sensitive search for "red" returns only "red." If you select this check box, the same query will return red, RED, and Red when using red as a record selection value. This option is selected by default. See also the Report Options dialog box.

However, my understanding from my time at Crystal was that this only works if the database is set to be case insensitive:
From the SQL server page on MSDN:
In the Collation Settings dialog box, you have two options:
To make a Windows Locale collation case-sensitive, select Collation designator and then select the correct collation designator from the list. Clear the Binary check box, and then select the Case-sensitive check box.
So it really depends upon your database settings. Andrew Baines
Chase International

 

[Ngolem]

Hmmm I always assumed everything was case sensitive...will have to look into it more...thanks Jim
JimBroadbent@Hotmail.com