BirgittaHauser

-- Convert a numeric date consisting of 3 Columns (4-digit year, 2-digit month, 2-digit day) into a real date
-- If Year, Month, Day are all *Zeros - 0001-01-01 is returned
-- If Year, Month, Day are all 9      - 9999-12-31 is returned
-- If Year, Month, Day deliver an invalid date - 8888-12-31 is returned
-- Otherwise the converted date is returned

Create Or Replace Function YourSchema/CvtYYYY_MM_DD2Date (
          Paryear Decimal(4, 0)  Default 0, 
          Parmonth Decimal(2, 0) Default 0,
          Parday Decimal(2, 0)   Default 0)

BirgittaHauser

Create Function YourSchema/Checknum (Parstring Varchar(32))
       Returns Integer
   Language Sql
   Modifies Sql Data
   Deterministic
   Called On Null Input
   Secured
   Begin
     Declare LocNum  Decimal(31, 9) Not NUll Default 0;
     Declare Continue Handler For SQLException Return -1;

BirgittaHauser

CREATE OR REPLACE FUNCTION YourSchema.NUM72DATE ( 
	DATENUM DECIMAL(7, 0) ) 
	RETURNS DATE   
	LANGUAGE SQL 
	DETERMINISTIC 
	CALLED ON NULL INPUT 
BEGIN 
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
           RETURN DATE ( '8888-12-31' ) ; 
   RETURN CAST ( DIGITS ( DEC ( CHAR ( DATENUM + 19000000 ) , 8 ) ) CONCAT '000000' AS DATE ) ; 

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

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

-- 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

-- 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

-- 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

-- 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

-- 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');