RE: SQL how to fix

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.