Print Page | Close Window

UpperCase function on parameter field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8823
Printed Date: 04 May 2024 at 6:02am


Topic: UpperCase function on parameter field
Posted By: teddybear
Subject: UpperCase function on parameter field
Date Posted: 06 Jan 2010 at 9:15pm

Hi there,

I have a static string parameter field with "Allow multiple values" set to True. My goal is to convert the user input on this parameter to upper case as the value store in the database are in uppercase.
 
I tried this in the Select Expert :
 
UpperCase({?parameter1}) and I got the following error message :
 
The array must be subscripted. For Example: Array.
 
I am on Crystsal Report XI. Can someone advise.
 
Thanks in Advance


Replies:
Posted By: kevlray
Date Posted: 07 Jan 2010 at 8:28am
I know that on SQL data sources that string parameter comparisons are case-in< ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 11">< name="Originator" content="Microsoft Word 11"><> sentitive.  So, you may not need to do any conversion.

Posted By: hilfy
Date Posted: 07 Jan 2010 at 9:32am
Actually, that depends on the type of database you're using and how it's configured.  Oracle IS case sensitive unless the DBA has set it to not be.
 
teddybear, can you post the whole formula from the Select Expert (not just this piece, but anything else you might have in there as well)?
 
Thanks!
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: teddybear
Date Posted: 07 Jan 2010 at 5:56pm
Hi there,
 
Thanks for the reply.
 
This is the whole formula in the Select Expert
 
UpperCase({ITEM_CODE}) = UpperCase({?Element})
 
Where ITEM_Code is the datbase field and ?Element isthe parameter field.
 
I am on Oracle 9i
 
 
 

Posted By: hilfy
Date Posted: 08 Jan 2010 at 7:00am
Did you just type this in?  Usually fields are in the format {table.fieldname} - I don't see the table name in your reference to the ITEM_CODE field.  Or is this just a typo in your post?
 
If it's just a typo in your post, try creating a formula that does the UpperCase({?Element}) and then using the formula in your Select Expert.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: teddybear
Date Posted: 10 Jan 2010 at 4:16pm
HI Dell,
 
You are right, the complete formula shouls be as follow :
 
UpperCase({ITEM.ITEM_CODE}) = UpperCase({?Element})
 
where ITEM is the table name.
 
Let me just add on this.
 
When I declare the parameter field as "Allow multiple values"= FALSE, I did not get the error message :The array must be subscripted. For Example: Array.

Posted By: hilfy
Date Posted: 11 Jan 2010 at 6:48am
That is correct, you cannot do an UpperCase on multiple values from a parameter this way.  If you're familiar with SQL, this makes sense - Crystal replaces parameters in the SQL it generates by using the actual value(s) entered for the parameter.  So, for example, when you enter a list of string values, you get something like this in the SQL (using Oracle syntax):
 
myTable.String_Field in ['Value1', 'Value2', 'Value3']
 
If you try to use UpperCase on the parameter, what Crystal will try to do is this:
 
myTable.String_Field in UpperCase(['Value1', 'Value2', 'Value3'])
 
This is invalid syntax for the database, you'd have to have something like this instead:
 
myTable.String_Field in [UpperCase('Value1'), UpperCase('Value2'), UpperCase('Value3')]
 
But, if you have just a single value, it works with no problems:
 
myTable.String_Field = UpperCase('Value1')
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: teddybear
Date Posted: 11 Jan 2010 at 4:38pm

Hi Dell,

Thanks for the reply again. I do understand what you are trying to say.

In this case, do you have any suggestion on the work around ?

Posted By: hilfy
Date Posted: 11 Jan 2010 at 6:49pm
This question has been asked before and I've never found/seen a good answer to it.  I'm no longer working with Oracle, so I don't have access to documentation, but you may want to see if there's a way to turn case sensitivity off for a specific query For example, Set Define Off tells Oracle to not use ampersand to identify substitution parameters; I've used this when I ran insert statements that contained '&' in field values.  There may be something similar you can do for case sensitivity.  Or you may need to do an "Alter Session" command for this.  In any case, if something like this is available, you'll need to put the SQL for your report into a command as an annonymous PL/SQL block and use your Set or Alter Session command as the first line.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window