IBM i Global

 View Only
  • 1.  Replace RPG code with sql

    Posted Thu April 07, 2022 03:17 AM
    Edited by Paolo Salvatore Thu April 07, 2022 03:17 AM
    Hi I've a procedure inside an rpg program like this:

    Dcl-Pi SetPrg Zoned(8);                
      KNRif Like(mlenrif);                 
        
    End-Pi SetPrg;                         
     ______________________________________
                                           
    MlePrg = *HiVal;                       
                                           
    SetGt (KnRif :MlePrg)  MleEse0f;       
                                           
    ReadPE KnRif RMleEse;                  
                                           
    If %Eof(MleEse0f);                     
      MlePrg = 1;                          
    Else;                                  
      MLePrg += 1;                         
    EndIf;                                 
                                           
    MleNRif = KNrif;                       
    
    // valorize some other fields
      
    Write RMLeEse;                             
                                               
    Return MlePrg;                             ​


    The file MleEse0f have two keys, the firstone is my order number, and the second a simple progressive.
    This procedure receive in input the order number, make a setgt with the order number and then a readEP, So obtain the last progressive number add 1 and write the record. 

    How can I remove this logic with a sql?

    Many thanks. 



    ------------------------------
    Paolo Salvatore
    ------------------------------



  • 2.  RE: Replace RPG code with sql

    IBM Champion
    Posted Thu April 07, 2022 04:20 AM
      |   view attached
    Mr. Paolo

    By your "progressive number", I understand it is an integer value that increases by 1 for a new row added to the file.  If this is the case, you can simplify this part of SQL coding by letting Db2 insert this column value for you automatically by declaring this column as what is called an IDENTITY COLUMN.  This article can explains what this is : https://www.as400andsqltricks.com/2021/12/identity-column-in-ddl-table.html.  I also attach another article for your to read.

    If you use identity column, you just use a straightforward SQL statement INSERT to add the new order entry to the file right away while omitting the identity column value with a NULL.  Otherwise, you need to retrieve the current latest value of the progressive number first into an SQL variable and increase it before using it in the INSERT statement. 

    SELECT MAX(<progressive column>) INTO  <SQL variable> FROM  lib.table;
    <increase the column value by 1>
    INSERT INTO ......   



    ------------------------------
    Satid Singkorapoom
    ------------------------------

    Attachment(s)



  • 3.  RE: Replace RPG code with sql

    Posted Thu April 07, 2022 07:27 AM

    Hi Satiq, 

    many thanks for your answer, 

    there's only a bit difference, because I want to reset this progressive number every time then change the Knrif value, something like:


    ORDERKEY Progressive
    order1 1
    order1 2
    order2 1
    order3 1
    order2 2
    order3 2
    order1 3
    order4 1


    ------------------------------
    Paolo Salvatore
    ------------------------------



  • 4.  RE: Replace RPG code with sql

    IBM Champion
    Posted Thu April 07, 2022 09:08 PM
    Edited by Satid Singkorapoom Thu April 07, 2022 09:17 PM
    Mr. Paolo

    So, you want running sequence numbers for each unique order number. Then I think one way you can do in SQL is to use a IF EXISTS statement to check if the order number already exist or not.  If not, then you assign sequence number as 1. If it already exists, you increase its max value by 1.   The statement looks something like this :

    IF EXISTS (SELECT MAX(<progressive column>) INTO <SQL variable> FROM  lib.table WHERE <order number column> = <another SQL variable> )
    THEN  SET  <SQL variable> = <SQL variable> + 1
    ELSE  SET <SQL variable> = 1
    END IF ;
    INSERT ......  


    ------------------------------
    Satid Singkorapoom
    ------------------------------



  • 5.  RE: Replace RPG code with sql

    IBM Champion
    Posted Fri April 08, 2022 02:02 AM
    Edited by Birgitta Hauser Fri April 08, 2022 02:03 AM
    IMHO the easiest way is this statement:
    Select Coalesce(Max(MlePrg) + 1, 1) into :YourHostVar
    From YourTable
    Where KnRif = :Mlenrif;

    It will return the next MlePrg, if a row for knRif is found. If there is no row for KnRif it will return 1.
    You can embedd it in an RPG function or you may just write an SQL function which includes only this statement:
    Create Or Replace Function YourSchema/YourFunction (
    ParKnRif Varchar(15))
    Returns Integer
    Language Sql
    Return (Select Coalesce(Max(MlePrg) + 1, 1)
    From YourTable
    Where KnRif = ParKnRif);

    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------



  • 6.  RE: Replace RPG code with sql

    Posted Fri April 08, 2022 11:40 AM
    Hi all,
    many thanks for your help, I try this solutions:

    Select Progressivo 
      From Final Table (
          Insert Into Gestsimdat/DirEse0f
            Select current timestamp, 'E', 'M', 'I6910014202382', Coalesce(Max(Progressivo) +1, 1),
              'execiddiprova', nullif('', ''), Date(nullif(0, 0)), nullif('', ''), nullif('', ''),
              nullif('', ''), nullif('', 'tvtic'), nullif('', '')
              From Gestsimdat/Direse0f
              Where Riferimento = 'I6910014202382');​
    Many thanks to all

    ------------------------------
    Paolo Salvatore
    ------------------------------