BirgittaHauser

Create Or Replace Function RtvDspFFldPos                    
          (ParDspF    VarChar(10),                          
           ParDSpFLib VarChar(10)  Default '*LIBL')         
       Returns Table (DSPF       CHAR(10) ,                 
                      DSPFLIB    CHAR(10) ,                 
                      Format     Char(10) ,                 
                      ExtFldName CHAR(10) ,                 
                      Row        SmallInt ,                 
                      Column     SmallInt ,                 
                      LengthByte Integer  ,                 

BirgittaHauser

      //*********************************************************************************************
      // I recently get asked how to read a small PDF document into a BLOB and pass it as parameter  
      // to another function or procedure
      // 1. When working with embedded SQL character host variables can only be used up to 32k
      //    Larger data (up to 16 MB - RPG restriction) 
      //    can be read into a LOB-Variable defined with the SQLTYPE keyword
      // 2. Unfortunately the SQLTYPE Keyword cannot be used in a Prototype or Procedure Interface
      // 3. But the SQL precompiler generates for the LOB variable a data structure with an
      //    UNS(4)     Subfield _LEN and
      //    CHAR(xxx)  Subfield _DATA

BirgittaHauser

Select System_Table_Schema, System_Table_Name, System_Table_Member, Line_Number, Line 
  from Qsys2.SysPartitionStat cross join 
       Lateral(Select Line_Number, Cast(Line as VarChar(256)) Line
                 from Table(QSYS2.IFS_READ(Path_Name => '/QSYS.Lib/' 
                                                        concat Trim(System_Table_Schema) concat '.lib/' concat    
                                                               Trim(System_Table_Name)   concat '.file/' concat
                                                               Trim(System_Table_Member) concat '.mbr'))) x   
  Where     System_Table_Schema = 'YOURSCHEMA'
        and System_Table_Name   = 'YOURSRCF' 
--  Order By System_Table_Schema, System_Table_Name, System_Table_Member, Line_Number                                                                                                   

BirgittaHauser

-- Retrieve the IFS file with GET_CLOB_FROM_FILE
-- Replace & with & 
-- Write a new IFS file with the same name as the original file and replace the existing IFS file
Call Qsys2.Ifs_Write_Utf8(Path_Name   => '/Home/Dir1/Dir2/YourIfsFile.csv', 
                          Line        => Replace(Get_Clob_From_File('/Home/Dir1/Dir2/YourIfsFile.csv'), 
                                                 '&', '&'), 
                          Overwrite   => 'REPLACE', 
                          End_Of_Line => 'NONE');            

BirgittaHauser

-- Question in an Forum: I have a table/file which has duplicate records (Lets say there are 10 fields in a record). 
--                       Out of which, if 5 fields put together are same across more than 1 record, then it is considered as a duplicate). 
--                       I need to delete those duplicate records alone using a SQL statement embedded in a SQLRPGLE program.

-- 1. Solution with a Cursor in embedded SQL (using a Common Table Expression for determining the duplicate rows!)
**Free
//---------------------------------------------------------------------------------------------
  CTl-Opt DatFmt(*ISO)  Option(*NoDebugIO);
//---------------------------------------------------------------------------------------------
  DCL-S  GblRRN   Int(20);

BirgittaHauser

-- UDF - Check eMail Address:
-- Parameters: ParEmail => EMail Address
-- Returns:    0 = Invalid eMail Address / 1 = valid eMail Address
--             In Release 7.5 the new Boolean Datatype could be used for the Return Value
-- Attention:  There might be better regular expression patterns for checking eMail addresses

Create or Replace Function YourSchema.CheckEmail (ParEMail Varchar(256))
       Returns Integer
   Language SQL
   Modifies SQL Data

BirgittaHauser

-- Search thourhg IFS files that include a specific text (sequence of characters)
-- In the following example the '/home/Hauser' directory is searched for streamfiles with the extender .json
-- All these streamfiles are searched for 'FirstName'. 
-- All streamfiles including 'FirstName' are returned.

-- Attention: Get_CLOB_From_File must run under commitment control!
Select Path_Name, Get_Clob_From_File(Path_Name) "IFS File Content"
  From Table (Qsys2.Ifs_Object_Statistics(Start_Path_Name     => '/home/Hauser', 
                                          Subtree_Directories => 'YES',
                                          Object_Type_List    => '*ALLSTMF')) x

BirgittaHauser

-- Scan through all spoolfiles in a specific outqueue (e.g. QEZJOBLOG) for a specific string
Select a.Job_Name, Spooled_File_Name, File_Number, Spooled_Data
       -- , a.*
  from OutPut_Queue_Entries a Cross Join 
       Lateral(Select *
                 From Table(SysTools.Spooled_File_Data(
                                        Job_Name            => a.Job_Name, 
                                        Spooled_File_Name   => a.Spooled_File_Name, 
                                        Spooled_File_Number => File_Number))) b
  Where     Output_Queue_Name = 'QEZJOBLOG'

BirgittaHauser

-- User Defined Table Function: LicInfo_QLZARCAPI
------------------------------------------------------
-- Call QLZARCAPI - Retrieve the information written into the Joblog and split it into Description and Value
Create Or Replace Function YourSchema.LicInfo_QLZARCAPI ()
       Returns Table (LicDescr VarChar(256),
                      LicValue VarChar(256))
       Language SQL
       Specific xQLZARCAPI
       Deterministic
       Modifies SQL Data

BirgittaHauser

-- SQL Function: Get_Nth_Element - Return the nth Element in a String
-- Parameters: ParString    = String to be searched and split
--             ParDelimiter = Delimiter for splitting into Elements
--             ParElement   = the position of the Element to be returned
Create Function YourSchema/Get_Nth_Element (
         ParString    Clob(1M), 
         ParDelimiter Varchar(10) Default ';',
         ParElement   Integer     Default 1)
   Returns Varchar(4096)
   Language SQL