|
midrange.com
Charles,
I didn't know you could create indices in specific sort modes like that.
Where we've needed fast case insentive searching, I've duplicated the
field, so instead of
select * from table where upper (field) = 'VALUE'
if "field" is say 50 long, I created 2nd field called "upperfield", also 50
long, that is always set to the same value as field, but shifted into upper
case. Then we can create an index over it and do
select * from table where upperfield = 'VALUE'
and it'll use the index. I've also done similar for situations where I
need to trim the field value. There's some fields in our database in
particular where because the fields are enforced to be unique, the users
put spaces at the front to bypass this :-| e.g. "ABC", " ABC" and " ABC",
but then they want to be able to do a search for = "ABC" and still find all
3 records. So again I made an uppercased & trimmed copy of the field value
and created an index over this - or do you have any alternative suggestions
to speed up trim () queries as well?
Thanks,
Nigel Gay.
|---------+------------------------------>
| | "Wilt, Charles" |
| | <CWilt@xxxxxxxxxxxx|
| | > |
| | Sent by: |
| | java400-l-bounces@m|
| | idrange.com |
| | |
| | |
| | 29/06/2005 11:57 AM|
| | Please respond to |
| | Java Programming on|
| | and around the |
| | iSeries / AS400 |
| | |
|---------+------------------------------>
>--------------------------------------------------------------------------------------------------------------|
|
|
| To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx> |
| cc:
|
| Subject: RE: SQL In Java
|
>--------------------------------------------------------------------------------------------------------------|
Note that josep's suggestion of
jdbc:as400://AS400;date format=iso;sort=language;sort weight=shared
Will perform better than using upper().
By using upper(), the query engine can't make use of any indexs.
You'll want to create an index from a session with sort weight = shared.
(or via STRSQL, hit F13 and change the "Sort sequence" attribute to
*LANGIDSHR).
HTH,
Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
> -----Original Message-----
> From: java400-l-bounces@xxxxxxxxxxxx
> [mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Eric Lee
> Sent: Wednesday, June 29, 2005 8:13 AM
> To: Java Programming on and around the iSeries / AS400
> Cc: Java Programming on and around the iSeries / AS400;
> java400-l-bounces@xxxxxxxxxxxx
> Subject: Re: SQL In Java
>
>
> Ron,
>
> Why don't you just change your statement to account for case?
>
> eg.
> select * from table where upper(tabledata) like upper(searchstring)
>
> Eric
>
>
>
>
> RPower@xxxxxxxxxx
> Sent by: java400-l-bounces@xxxxxxxxxxxx
> 29/06/2005 10:39
> Please respond to
> Java Programming on and around the iSeries / AS400
> <java400-l@xxxxxxxxxxxx>
>
>
> To
> "Java Programming on and around the iSeries / AS400"
> <java400-l@xxxxxxxxxxxx>
> cc
>
> Subject
> SQL In Java
>
>
>
>
>
>
> Hitting a DB2, and trying to get the SQL statement to ignore
> the case. Is
>
> that possible?
>
> Ron Power
> Programmer
> Information Services
> City Of St. John's, NL
> P.O. Box 908
> St. John's, NL
> A1C 5M2
> Tel: 709-576-8132
> Email: rpower@xxxxxxxxxx
> Website: http://www.stjohns.ca/
> ______________________________________________________________
> _____________
> Success is going from failure to failure without a loss of
> enthusiasm. -
> Sir Winston Churchill
> --
> This is the Java Programming on and around the iSeries /
> AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>
>
>
>
> **************************************************************
> ****************************
> 'This e-mail message is intended solely for the person to
> whom it is addressed and may contain confidential
> or privileged information. If you have received it in error,
> please notify postmaster@xxxxxxxxxxxx and
> destroy this e-mail and any attachments. In addition, you
> must not disclose, copy, distribute or take any
> action in reliance on this e-mail or any attachments. Any
> views or opinions presented in this e-mail are
> solely those of the author and do not necessarily represent
> those of the company. When addressed to our
> customers, any quotations contained in this e-mail are
> subject to contract and are on the terms of The
> Company's standard Conditions, a copy of which is available
> on request. Any errors or omissions in any
> quotations or other information issued by The Company shall
> be subject to correction without any liability
> on the part of The Company. Due to the nature of Internet
> communications, DCS cannot guarantee that this
> communication, or any attachments, do not contain software
> viruses. We have taken every precaution to
> minimize this probability but cannot accept any liability for
> damage which you may sustain as a result of
> software viruses. We recommend you carry out your own virus
> checks before opening attachments.'
>
> DCS Transport & Logistics Solutions Ltd.
> Registered office: Newstead House, Lake View Drive, Sherwood
> Park, Annesley, Nottinghamshire, NG15 0DT
> Registered in England no: 3373490
> **************************************************************
> ****************************
>
> --
> This is the Java Programming on and around the iSeries /
> AS400 (JAVA400-L) mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>
>
--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.
********************************************************************************
The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee; access to this
email by anyone else is unauthorised.
If you are not the intended recipient: (1) you are kindly requested
to return a copy of this message to the sender indicating that you
have received it in error, and to destroy the received copy; and (2)
any disclosure or distribution of this message, as well as any action
taken or omitted to be taken in reliance on its content, is prohibited
and may be unlawful.
********************************************************************************
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
