× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2006

SQL scratchpad example

Here's an example of using the SQL scratchpad to create an SQL function that can be used in a SELECT stmt to show a row# for each displayed line, e.g. 

SELECT counter(),customer,name FROM custmast

1   ABC   Acme Stuff
2   DEF   Bulldozers, Inc.
3   GHI   World Wide Pants

Put the following procedure into a service program:

p Counter...
p b export d Counter... d pi d Output 10i 0 d OutputNullInd 5i 0 d SQLstate 1a d FunctionName 1a d SpecFuncName 1a d MsgText 1a d ScrPad 100a d ScratchPad ds qualified based(ptrScrPad) d len 10i 0 d countr 10i 0 d not_used 96a ptrScrPad = %addr(ScrPad); ScratchPad.countr += 1; Output = ScratchPad.countr; OutputNullInd = 1; return; /end-free p e 


Then in SQL do

CREATE FUNCTION MYLIB/COUNTER()
RETURNS INT SCRATCHPAD NOT DETERMINISTIC NO SQL NO EXTERNAL ACTION LANGUAGE RPGLE PARAMETER STYLE DB2SQL EXTERNAL NAME 'MYLIB/MYSVCPGM(COUNTER)' DISALLOW PARALLEL;
I can't remember where I got this, but thanks to whomever came up with it -- it's been useful. 

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.