RE: Updating by row count in Interactive SQL -- MIDRANGE-L

Actually, as I look at the manual some more...

You might not need the CTE or NTE with row_number. I don't have v5r4, so I've not add a chance to use
it.

But it looks like this might work....
UPDATE thisTABLE
set RECCNT = row_number() over ( )
where date1 > 20080000

I think that will number the records in arrival seq.

Not entirely sure the "over" part can be blank, you might need to have
"over ( order by someColumn )"

HTH,

Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Friday, March 07, 2008 8:19 AM
To: Midrange Systems Technical Discussion
Subject: RE: Updating by row count in Interactive SQL

Mark,

Are you on v5r4?

If so, check out the ROW_NUMBER() OLAP functionality. You'll probably
need to use it in a Common
Table Expression or Nested Table expression...

Otherwise, you need a custom UDF that takes advantage of the "scratchpad"
available to store and
update the counter. You should be able to find examples online for this.

HTH,

Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Mark Villa
Sent: Friday, March 07, 2008 8:12 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Updating by row count in Interactive SQL

Hi all,
Any suggestions on how you would do this in interactive SQL?

UPDATE thisTABLE
counter = counter + 1
set RECCNT = counter
where date1 > 20080000

An example would be most appreciated. The above, obviously does not

work.

The only two options I have in this case are SQL and UPDDTA record by
record.

BTW, every now and then I see answers posted asking a good question -
why would you want to do that? - the hard way? I suspect it is usually
because we are working on a box with restrictions, no developer
environment, etc.

When we are in our native environment these questions (with simple
answers in another tool) don't pop up.

--
Mark
--
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 e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are not a
named addressee you are hereby notified that you are not authorized to
read, print, retain, copy or disseminate this communication without the
consent of the sender and that doing so is prohibited and may be unlawful.
Please reply to the message immediately by informing the sender that the
message was misdirected. After replying, please delete and otherwise
erase it and any attachments from your computer system. Your assistance
in correcting this error is appreciated.
--
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 e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.