Inhoud blog
  • Original CL vs. ILE CL
  • Windows and Other Functions in iSeries Access PC5250
  • XML, Namespaces, and Expat
  • Quick Download of All Source Members to a PC
  • Retrieve Source Code of ILE CL
    Zoeken in blog

    Qmma developer network

    29-11-2006
    Klik hier om een link te hebben waarmee u dit artikel later terug kunt lezen.Sum character fields with decimals in SQL on the iSeries
    SELECT sum(case                                     
           when trim(mcm_ucop) = '' then 0              
           else cast(trim(mcm_ucop) as dec(11, 3))      
           end) as ucop,                                
           sum(case                                     
           when trim(mcm_tadp) = '' then 0              
           else cast(trim(mcm_tadp) as dec(11, 3))      
           end) as tadp                                 
    FROM mcm_
           

    Field mcm_ucop and mcm_tadp are 15 characters : example  0000005187.570                                    

    29-11-2006 om 00:00 geschreven door Qmma  


    10-11-2006
    Klik hier om een link te hebben waarmee u dit artikel later terug kunt lezen.The Parse( ) procedure

    I frequently have to create strings that consist of some known data and some variables. This requirement often leads me to write a rather complicated expression, such as the following one:

     /free
       cmdstr = 'CRTPF FILE(' + %trim( Lib ) + '/' + %trim( File ) +
                ') TEXT(' + %trim( Text ) + ') RCDLEN(100)';
     /end-free

    This type of expression can be a hassle to code. Mistakes (e.g., missing an apostrophe) are easy to make, and it's often unclear what the created string will be. To make this coding easier for myself, I wrote a utility named Parse. Using this utility, I can code the following:

     /free
       cmdstr = parse( 'CRTPF FILE(&1/&2) TEXT('&3') RCDLEN(100)' :
                       %trim( Lib ) : %trim( File ) : %trim( Text ) );
     /end-free

    The parse() procedure accepts one required parameter (the base string, containing substitution variables) and up to nine additional optional parameters (the substitution values themselves). The base string can contain up to nine separate substitution variables, in the form &n, where n is a number from 1 to 9, which refers to a substitution value parameter. A substitution variable can occur multiple times in the base string.

    In the preceding example, the &1 variable is replaced with the first optional parameter, which is %trim(Lib). The &2 substitution variable is replaced with %trim(File), and so forth.

    All the parameters are defined as pointers, with the OPTIONS(*STRING) keyword, which means that you can pass either a hard-coded string or a variable to the parse() procedure. Note that if a variable is passed, trailing blanks are included unless it's defined with the VARYING keyword or is passed with the %trim() or %trimr() built-in function (BIF).

    Some examples of using parse() might be as follows:

    D Base            C                   'My &1 is &2.'
    D Type            S             10A   Inz('life')
       .
       .
     /free
    
       // Example 1: Simple string replacement (1)
       String = parse( Base : 'name' : 'Rory Hewitt' );
       // Result 1: String = 'My name is Rory Hewitt.'
    
       // Example 2: Simple string replacement (2)
       String = parse( '&1&3&2 &1&4&2' : '(' : ')' : 'value1' 'value2' );
       // Result 2: String = '(value1) (value2)'
    
       // Example 3: Passing an untrimmed variable
       String = parse( Base : Type : 'good' );
       // Result 3: String = 'My life       is good.'
    
       // Example 4: Passing a trimmed variable
       String = parse( Base : %trim( Type ) : 'good' );
       // Result 4: String = 'My life is good.'
    
    /end-free

    If a substitution value parameter that lacks a corresponding substitution variable in the base string is passed, it is simply ignored. In the following example, the last parameter is def, but it's ignored because the Base string has no &3:

    D Base            C                   'My &1 is &2.'
       .
       .
       String = parse( Base : 'job' : 'worker bee' : 'def' );
       // Result: String = 'My job is 'worker bee'.'

    If a substitution variable is found in the base string but has no corresponding substitution value parameter, it remains in the base string. In the following example, the result will contain &2 because I did not pass enough parameters to supply a replacement for the &2 variable:

    D Base            C                   'My &1 is &2.'
       .
       .
       String = parse( Base : 'life' );
       // Result: String = 'My life is &2.'

    Substitution variables (e.g., &3) passed in substitution value parameters are not themselves parsed. So in the following code, &3 is simply treated like any other substitution value:

    D Base            C                   'My &1 is &2.'
       .
       .
       String = parse( Base : '&3' );
       // Result: String = 'My &3 is &2.'

    However, you could use that string as input to a subsequent call to parse() where it would be used. In this next example, the first call to parse() simply replaces the substitution variable &1 with the substitution value &3; when parse() is called a second time, &3 is now a substitution variable in the base string, so it's replaced:

    D Base            C                   'My &1 is &2.'
       .
       .
       String = parse( Base : '&3' );
       // Result: String = 'My &3 is &2.'
       String = parse( String : 'ignored' : 'Rory Hewitt' : 'name' );
       // Result: String = 'My name is Rory Hewitt.'

    Using the parse() procedure makes seeing what the eventual command string will be easier. My original CRTPF example code is much simpler to understand when using parse() than when using lots of string concatenation, because the base string looks very similar to the eventual command. In fact, in addition to the base string being hard-coded or defined as a constant in the D-specs or in a compile-time array, it could be retrieved from a file at runtime, thus allowing you more flexibility when creating command strings for different environments or outputting form letters or whatever.

    Creating Parse()

    Because parse() is simply a procedure, I included no information about how to compile it. I suggest that you either put it into an existing module (perhaps one that already contains similar string-handling procedures) or create a new one, which you would then bind into a service program containing other "generic" procedures, such as string-handling and numeric conversion. Any program that needs to call parse() must /COPY in the PARSE_P copybook.

    You can download the parse() utility from the following link:

    http://www.pentontech.com/IBMContent/Documents/article/53509_142_ParseUtil.zip

    10-11-2006 om 14:50 geschreven door Qmma  


    Klik hier om een link te hebben waarmee u dit artikel later terug kunt lezen.Stop Numeric Overflow

    You won't often find me extolling the virtues of an error message. After all, I pride myself on writing proper code. I don't want to receive errors. However, there's one error message that I think is one of the best enhancements ever made to the RPG language: RNQ0103 "Target for a numeric operation is too small to hold result."

    Until recently, I thought that you could get this message only if you did your math in an expression, such as those used on an EVAL opcode. I thought that the old-style math opcodes (e.g., ADD, SUB, DIV, MULT) could not produce this error. Today, I discovered that they can.

    The trick is the Truncate Numeric (TRUNCNBR) compiler option. You can specify TRUNCNBR(*NO) on the CRTRPGMOD, CRTBNDRPG, or H-spec to tell the compiler that you want the program to fail with an error if a value is too large to fit in the receiver variable. For example:

    CRTBNDRPG PGM(myProgram) TRUNCNBR(*NO)

    Why do I like this option so much? I like it because it prevents errors. Consider a report that lists all the payments that your accounting department needs to collect from customers. It totals all the invoices issued to each customer and subtracts all the previous payments to get the current balance. What happens if the total of invoices is too large for the receiver variable? You might not know that the customer owes anything! The customer might get away with short paying by thousands of euros!

    When you specify TRUNCNBR(*NO), the program can send an error message. If you've written a *PSSR to trap errors, that *PSSR can notify someone that a problem exists, so that they can take corrective action. If you don't use a *PSSR or some other technique that monitors for errors, the program crashes and the user calls you for help. 

    10-11-2006 om 00:00 geschreven door Qmma  




    Archief per maand
  • 04-2007
  • 03-2007
  • 02-2007
  • 01-2007
  • 12-2006
  • 11-2006
  • 10-2006
  • 08-2006
  • 07-2006
  • 09-2005

    E-mail mij

    Druk op onderstaande knop om mij te e-mailen.



    Blog tegen de wet? Klik hier.
    Gratis blog op https://www.bloggen.be - Meer blogs