On 17-Aug-2016 20:41 -0700, Jon Paris wrote:
On Aug 17, 2016, at 3:52 PM, CRPence wrote:
On 16-Aug-2016 06:11 -0700, Mark Murphy wrote:
I have added two new RFE's for your consideration:
[http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=93114]
[http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=93115]
These are to add new BIF's to RPG:
93114 is for %min(fld1: fld2: ...) and %max(fld1: fld2: ...)
93115 is for %right(str: int) and %left(str: int)
Yes I could write my own procedures for %max and %min, but I
could not write one max or min procedure to handle all cases, and
the BIF equivalent of %right using %subst is a bit convoluted.
%min, %max, %right, and %left are more readable, thus more
maintainable, and generic enough that they should be part of the
language.
Seems easy enough, to just code those using SQL [shown without any
error checking]:
exec SQL
set :min_Val = min( :fld1, …, exp_n, …)
;
exec SQL
set :max_Val = max( :fld1, …, exp_n, …)
;
exec SQL
set :str_Suffix = right( :str , :int_exp )
;
exec SQL
set :str_Prefix = left ( :str , :int_exp )
;
And if you don’t have SQL?
Well, I suppose one option is about the same as if the desired
function was already available in a newer version… Obtain\purchase the
upgrade to get that capability :-)
Another option, one already chosen, but with no immediate effect, was
opening [and others possibly voting for] an RFE, and then wait, wait a
lot longer, and finally, probably just keep waiting ;-)
I rarely give much thought any more, with regard to a lack of the SQL
pre-compiler, as that rarely has been mentioned as an issue, since for
many years that I can recall, with the more likely issue being that
someone might find a change from compiling with RPGLE to compiling with
SQLRPGLE to be undesirable [¿as if somehow debasing their RPG source?].
But having read that question, leaves me wondering, if any participants
on this list actually are legitimately in that situation.?
I suppose I could have suggested that writing the code using COBOL
instead of RPG, would get them both a MIN and a MAX function ;-)
FWiW, every system _has_ the SQL included as [an integral] part of
the IBM i OS, the DB2 for i, even if the system does not include the
separately purchased SQL pre-compiler from which comes the ability to
code SQL embedded in an HLL, such as RPG. Thus there are still ways to
accomplish the task using the SQL, even if not so nicely as with having
coded directly with embedded SQL; though possibly with the exception of
the SQL CLI, suggesting anything other than embedded SQL, probably would
be a lot like suggesting that COBOL could handle the request, which is
similarly unhelpful contrasted with just writing the code to do what is
required, with the language already being used.
Given how often both purely dynamic SQL [as concatenation of clauses
and variable-data values into a string] and dynamic CL requests are seen
posted in messages to this list, the following as an example of how the
SQL might be used [without access to HLL-embedded SQL], is unlikely too
bizarre for most anyone. The following strings could be easily enough
generated to produce a file that RPG could then user-open and read to
obtain the maximum value; in this case, using the MAX aggregate function
instead of the max scalar function as referenced in my prior reply:
fld1 = 17.46 ;
fld2 = -5045.12 ;
fldN = 764.06 ;
apostrophe = '''';
leftParen = '(' ;
rightParen = ')' ;
commaParen = ',(' ;
viewString = 'create or replace view qtemp.maxOfVals (maxVal) as'
+ '( with listOfVals ( nbrVal ) as'
+ ' ( values'
+ leftParen + %editc( fld1 : 'P') + rightParen
+ commaParen + %editc( fld2 : 'P') + rightParen
// fld3 ... fldN-1
+ commaParen + %editc( fldN : 'P') + rightParen
+ ' )'
+ ' select max( nbrVal ) from listOfVals'
+ ') rcdfmt maxOfValsR'
;
clRequest = 'runsql ' + apostrophe + viewString + apostrophe
+ ' commit(*none)' ;
qcmdExc(clRequest:%len(clRequest)) ; // issue RUNSQL CL request
open maxOfVals ; // Open the VIEW as input
read maxOfVals ; // Read the max value
msg = 'Max Value: ' + %editc(maxVal:'P') ; // prep output data
dsply msg ; // Output max value
So in effect, rather than using embedded SQL, the above dynamically
produces at run-time, something like the following VIEW [that could also
be used to create the file against which to effect a compile, to enable
the Dcl-F], and then obtains the data from that logical VIEW, just as a
row of data might be obtained from any other file using RLA:
create view qtemp.maxOfVals (maxVal) as
( with listOfVals ( nbrVal ) as
( values
( 17.46 )
,( -5045.12 )
,( 764.06 )
)
select max( nbrVal )
from listOfVals
) rcdfmt maxOfValsR
midrange.com
