RE: SQL how to fix

["Stone, Joel" <Joel.Stone@xxxxxxxxxx>]

OK thanks

                               Display Data Area       
                                                       
 Data area . . . . . . . :   QSS1MRI                   
   Library . . . . . . . :     QUSRSYS                 
 Type  . . . . . . . . . :   *CHAR                     
 Length  . . . . . . . . :   750                       
 Text  . . . . . . . . . :                             
                                                       
            Value                                      
 Offset      *...+....1....+....2....+....3....+....4..
     0      'V5R4M000                 2924             

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Schutte
Sent: Tuesday, October 30, 2012 3:09 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL how to fix

statement worked as is for me.  using a different file of course.  and a
field that was smaller.

I would rule out each thing as being the issue.

First.
Select row_number() over() from mdcmdct

Next
Select row_number() over() from mdcmdct
where translate(left(amtxt,65),'+','0123456789','+')
       like '%+++++++++%'

Last.
select row_number() over(),
      substr(left(amtxt,65),locate('+++++++++',
      translate(left(amtxt,65),'+','0123456789','+')),10),
      amtxt,translate(left(amtxt,65),'+','0123456789','+')

       from mdcmdct
      where translate(left(amtxt,65),'+','0123456789','+')
       like '%+++++++++%'

Hopefully that will lead you to the right thing that's not working.

FYI row_number() over() wasn't introduced until V5R4 I believe.  What
version are you on?


On Tue, Oct 30, 2012 at 3:59 PM, <rob@xxxxxxxxx> wrote:

> You need something in the over().  Something in the parenthesis.  One
> sample is
> select rownumber() over(order by myidcol ASC) as newid,
> idtest.*
> from rob/idtest
>
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   "Stone, Joel" <Joel.Stone@xxxxxxxxxx>
> To:     "'Midrange Systems Technical Discussion'"
> <midrange-l@xxxxxxxxxxxx>,
> Date:   10/30/2012 03:50 PM
> Subject:        SQL how to fix
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Any idea how to fix this?  It works great without the row_number() over()
>
>
> Thanks
>
>
>
>                               Enter SQL Statements
>
>  Type SQL statement, press Enter.
>       Function not supported for query.
>  ===> select row_number() over(),
>       substr(left(amtxt,65),locate('+++++++++',
>       translate(left(amtxt,65),'+','0123456789','+')),10),
>       amtxt,translate(left(amtxt,65),'+','0123456789','+')
>
>        from mdcmdct
>       where translate(left(amtxt,65),'+','0123456789','+')
>        like '%+++++++++%'
>
> ______________________________________________________________________
> This outbound email has been scanned for all viruses by the MessageLabs
> Skyscan service.
> For more information please visit http://www.symanteccloud.com
> ______________________________________________________________________
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.