RE: Updating by row count in Interactive SQL

["Wilt, Charles" <WiltC@xxxxxxxxxx>]

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.