Focal Point
Case Sensitive
November 21, 2007, 09:36 AM
SGCase Sensitive
HELLO,
Is there any SET COMMAND which will ignore case?
I have data with lower and upper case in a database and my where command is not working.
Thanks,
SG
Webfocus 7.6.8
PDF,HTML & EXCEL
November 21, 2007, 10:01 AM
FrankDutchWhat I would do is use the function UPCASE
you ask for the string to select
-DEFAULT &ASKEDFOR='selected';
and convert it to uppercase.
-SET UPINPUT = UPCASE(4, '&ASKEDFOR.EVAL');
Then in the define you do the same with the field from the database
DEFINE FILE XXX
UPSTRING/A4=UPCASE(4,MIXEDSTRING,UPSTRING);
END
In the where statement you compare the field form the database with the amper value
...
WHERE UPSTRING EQ UPINPUT;
...
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
November 21, 2007, 10:05 AM
SGFrankDutch,
Thanks for the information but I was wondering if there is any SET command that will ignore the case. I have several where statements and several fex files that needs to be fixed.
Thanks,
SG
Webfocus 7.6.8
PDF,HTML & EXCEL
November 21, 2007, 10:59 AM
Tony ASG,
WHat is your data repository? It might be possible within that (e.g. MS SQL).
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
November 21, 2007, 11:04 AM
Francis MarianiThere is no WebFOCUS command for this. Some dbms have the capability. I think you should search for "ignore case" on the web for your dbms.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 21, 2007, 01:33 PM
Francis MarianiUnfortunately, I believe the column's indexes will not be used when selecting using the defined field - as well, this probably won't be passed to the dbms.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 21, 2007, 02:59 PM
FransHi SG,
The place where case sensitivity should be handled is in the DBMS. WebFOCUS isn't really doing the selection but your DBMS does.
In some cases this is a setting on your database, also using LIKE can help in some cases. But it depends mostley on your DBMS.
Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
November 21, 2007, 03:15 PM
SGTony A
we are using Progress(9.1D) Database.
Tom Flynn
Thanks for the suggestion but I don't want to use Define as you have already mentioned refreshing metadata can cause lot of problems.
Francis and Frans:
Thanks and I will look into it.
SG
Webfocus 7.6.8
PDF,HTML & EXCEL
November 22, 2007, 01:45 PM
FrankDutchSG
Although you do not want it, my solution is the only that works.
If you only do a define then the output is uppercase, but the user can still put in a lower or mixed case selection, so the result won't be what you want, unless you instruct the users to only key in uppercase characters.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |