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.
> 
>