RE: "select * from myfile where cusno like '1%' " fails on V5R3

Also note:

You're going to get any records where custno starts with 1, 100, 1234,
12345, 123456, ect, as the CAST function trims lead zeros.

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 Wilt, Charles
> Sent: Thursday, December 08, 2005 7:29 AM
> To: Java Programming on and around the iSeries / AS400
> Subject: RE: "select * from myfile where cusno like '1%' " 
> fails on V5R3
> 
> V5r3 includes a "enhancement" whereby the query engine will
> automatically CAST a column from one type to another.
> 
> So what's really being run on your v5r3 box is the following:
> select  * from myfile  where CAST(cusno as char) like '1%'
> 
> In v5r2, you have to do the CAST explicitly.
> 
> Note: using CAST against cusno in the where clause like this 
> means that
> the query engine will not be able to make use of an index.
> 
> 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 Bruce Jin
> > Sent: Tuesday, December 06, 2005 5:01 PM
> > To: Java Programming on and around the iSeries / AS400
> > Subject: Re: "select * from myfile where cusno like '1%' " 
> > fails on V5R3
> > 
> > Sorry I got my boxes messed up.
> > 
> > Indeed, this query works on V5R3. It does not work on V5R2. 
> This is a 
> > better scenario.
> > 
> > Marshall Dunbar/DPS wrote:
> > 
> > >I'm on V5R2 and if I try to use the LIKE predicate on a 
> > numeric field, I 
> > >get the same error message,
> > >===
> > >Marshall Dunbar
> > >DPS, Inc.
> > >marshall@xxxxxxxxxxx
> > >direct:(828)635-5561
> > >main office:(317)574-4300 (800)654-4689
> > >====
> > >
> > >
> > >
> > >Bruce Jin <brucej@xxxxxxxxxxxxxxxxxxxx> 
> > >Sent by: java400-l-bounces@xxxxxxxxxxxx
> > >12/06/2005 04:28 PM
> > >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
> > >"select  * from myfile  where cusno like '1%' " fails on V5R3
> > >
> > >
> > >
> > >
> > >
> > >
> > >This is an odd thing on V5R3. If cusno is a numeric field 
> then this 
> > >query will fail:
> > >
> > >select  * from myfile  where cusno like '1%'
> > >
> > >STRSQL complains: "Operand not valid in LIKE predicate".
> > >
> > > We will have to cast numeric field to character to resolve this.
> > >
> > > It is odd because same query works on V5R2.
> > >(It also works in MySQL and MS SQL server).
> > >
> > > Could you STRSQL on your box and verify this?
> > >
> > > Thanks.
> > >
> > >  
> > >
> > -- 
> > 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.
> 
>