CL:CHGLIBL LIBL(QTEMP ComSQLQry Systools QGPL);

--****************************************************************************************************
-- DATA CENTRIC
--****************************************************************************************************
-- Views
--====================================================================================================
-- Quarterly Sales
----------------------------
Select * from Sales;
Select * from AddressX;

Select   CustNo, Year(SalesDate) as SalesYear,  
         sum(case when Quarter(SalesDate)= 1 then Amount else 0 end) as Q1,  
         sum(case when Quarter(SalesDate)= 2 then Amount else 0 end) as Q2,  
         sum(case when Quarter(SalesDate)= 3 then Amount else 0 end) as Q3,  
         sum(case when Quarter(SalesDate)= 4 then Amount else 0 end) as Q4,  
         sum(Amount) as Total                                               
   from  Sales                                                        
   Group by CustNo, Year(SalesDate)
   -- group by grouping sets((CustNo, Year(SalesDate)), (CustNo), ())
   Order By CustNo, SalesYear
;

Drop View ComSQLQry.SalesQuart;

Commit;

Create or Replace View ComSQLQry.SalesQuart as
Select   CustNo, 
         Year(SalesDate) as SalesYear,  
         Cast(sum(case when Quarter(SalesDate)= 1 then Amount else 0 end) as Dec(11, 2)) as Q1,  
         Cast(sum(case when Quarter(SalesDate)= 2 then Amount else 0 end) as Dec(11, 2)) as Q2,  
         Cast(sum(case when Quarter(SalesDate)= 3 then Amount else 0 end) as Dec(11, 2)) as Q3,
         Cast(Sum(Case When Quarter(SalesDate)= 4 Then Amount else 0 End) as Dec(11, 2)) as Q4,
         Cast(Sum(Amount) as Dec(11, 2)) as Total
  From Sales
  GRoup By CustNo, Year(SalesDate);       
Commit;

Select * from SalesQuart;

-- Quarterly Sales with Address Information
--------------------------------------------------------
-- Drop View ComSQLQry.SalesQCust;
-- Commit

Create or Replace View ComSQLQry.SalesQCust
    as Select a.CustNo as ACustNo, SalesYear, Q1, Q2, Q3, Q4, Total, 
              b.*
          from SalesQuart a Left outer join AddressX b on a.CustNo = b.CustNo;


Select * from SalesQCust;

Commit;

Select SalesYear, ACustNo, CustName1, City, Q1, Q2, Q3, Q4, Total
from SalesQCust
Where SalesYear = 2009 and ACustNo in ('10003', '10005');

--====================================================================================================
-- Global Variables
--====================================================================================================  
-- View with Global Variables
------------------------------------------------------------------------------------------------------
-- Recursion: Global Variables
---------------------------------
Drop Variable ComSQLQry.GblDeparture;
Drop Variable ComSQLQry.GblArrival;
Drop Variable ComSQLQry.GblMaxConnects;

 Create or Replace Variable ComSQLQry.GblDeparture   VarChar(50) Default 'Frankfurt';
 Create or Replace Variable ComSQLQry.GblArrival     VarChar(50) Default 'Berlin';
 Create or Replace Variable ComSQLQry.GblMaxConnects Integer Default 3;

Commit;
Stop;

-- Create View with global variables
--------------------------------------
Select * from Flights
Order By Departure, Arrival;

Drop view ComSQLQry.FlightV03;
Commit;
Stop;

Create Or Replace View ComSQLQry.FlightV03
    as Select Connect_By_Root Departure as Departure, Arrival,
              Cast(Connect_By_Root Departure concat Sys_Connect_By_Path(Arrival, ' -> ') 
                   as VarChar(1028)) as Itinerary,
              Level - 1 as NbrConnect,
              Calculate(Sys_Connect_By_Path(VarChar(Price), '+')) as Costs
          From Flights
          
          Where     Arrival             = GblArrival
                And Connect_By_isCycle  = 0
                and Level              <= GblMaxConnects + 1
          
          Start With Departure = GblDeparture
          Connect By NoCycle Prior     Arrival = Departure
                                   and Arrival <> GblDeparture;


Commit;

-- Values GblConnect;
Set GblDeparture = 'München', GblArrival = 'Hamburg', GblMaxConnects = 2;

 Select Departure, Arrival, Itinerary, Costs 
 from flightV03
 Order By Costs;

 Set GblDeparture = 'München',   GblArrival = 'Hamburg', GblMaxConnects = 2;
 Set GblDeparture = 'Frankfurt', GblArrival = 'Berlin',  GblMaxConnects = 3;
 Set GblDeparture = 'Berlin',    GblArrival = 'Köln',    GblMaxConnects = 2;

 Select * from flightV03
 Order By Costs 
 Limit 1;
--****************************************************************************************************
-- SQL Routines
--****************************************************************************************************
-- Stored Procedures
--====================================================================================================
-- Calculating Workdays
-----------------------------------
Create Or Replace Procedure ComSQLQry.GetWrkDay_SP
                 (In  ParStrDate  Date    Default Current_Date,
                  In  ParEndDate  Date    Default Current_Date,
                  Out POutNbrDays Integer)
	      Language SQL
	      Specific GETWRKDSP
	      Deterministic
	      Called on NULL Input
	      
	      Set Option  DBGVIEW   = *SOURCE

BEGIN
   Declare LocDayOfWeek   SmallInt Default 0;
   Declare LocDate        Date     Default '0001-01-01';

   Set LocDate      = ParStrDate;
   Set LocDayOfWeek = DayOfWeek_ISO(ParStrDate);
   Set POutNbrDays  = 0;

   BegRepeat:
      Repeat If LocDayOfWeek < 6 Then Set POutNbrDays = POutNbrDays + 1;
             End If;
             If LocDayOfWeek = 7 Then Set LocDayOfWeek = 1;
             Else Set LocDayOfWeek = LocDayOfWeek + 1;
             End If;
             Set LocDate = LocDate + 1 Days;

      Until LocDate > ParEndDate
      End Repeat;

   Return POutNbrDays;                                              
End  ;

 Comment on Specific Procedure ComSQLQry.GETWRKDSP
      is 'Determine Number of Workdays';
      
Commit; 
Stop;

Call GetWrkDay_SP(Current_Date , Date('2024-12-31') , ? ); 

With Calendar (rundate) as (Values(Current_date)
                            Union All
                            Select Rundate + 1 Day From Calendar
                            where Rundate < Right(Year(Current_Date), 4) concat '-12-31')
Select Count(*) -- RunDate
From Calendar 
Where DayOfWeek_Iso(rundate) < 6;

--====================================================================================================
-- User Defined Functions
--====================================================================================================
-- Convert Date
------------------------------------------------------------------------------------------------------
Create or Replace Function ComSQLQry.CVTDATE (                        
       ParDateNum Dec(8, 0) )                     
       Returns DATE                              
       Language SQL                              
       Specific CVTDATE               
       Deterministic                         
       Reads SQL Data                            
       Called on NULL Input                      
       Disallow Parallel                         
       Set Option  DBGVIEW   = *SOURCE       
                                           
BEGIN                                                
   Declare Continue Handler For SQLEXCEPTION         
   Return Date('8888-12-31') ;                       
   Return Date(Digits(ParDateNum) Concat '000000');   
END  ;          

Commit;

Select NumDate, CvtDate(NumDate) Converted_Date
from NumDate a;
------------------------------------------------------------------------------------------------------
-- Get Monday from year / week
------------------------------------------------------------------------------------------------------
Create or Replace Function ComSQLQry.GetMondayFromYearWeek 
         (ParYear     Decimal(4, 0)  Default (Year(Current_Date)),
          ParWeekISO  Decimal(2, 0)  Default (Week_ISO(Current_Date)))
       Returns Date
    Language SQL
    Specific MONYYYYMM
    Deterministic 
    Modifies SQL Data
    Called On NULL Input
    
    Set Option DBGView = *Source
               
Begin
   Declare Jan4  Date  Default '0001-01-01';
   Declare Continue Handler 
           For SQLException Return Date('0001-01-01');               
   
   Set Jan4 = Date(Digits(ParYear) concat '-01-04');
   
   If   ParWeekISO < 0 
     Or ParWeekISO > Week_ISO( Jan4 - 1 Year - 7 Days)
     Then Return Date('0001-01-01');
   End IF;   
   
   Return Jan4 + (((ParWeekIso - 1) * 7) - DayOfWeek_ISO(Jan4) + 1) Days;
End;    
Commit;

Values(GetMondayFromYearWeek(2024, 2)),
      (GetMondayFromYearWeek(2024, Week_ISO(Current_Date)));        

------------------------------------------------------------------------------------------------------
-- SQL UDF - Calculate
------------------------------------------------------------------------------------------------------
DROP SPECIFIC FUNCTION IF EXISTS COMSQLQRY/CALCUCLOB;
DROP SPECIFIC FUNCTION IF EXISTS COMSQLQRY/CALCULATE;
Stop;

Create or Replace Function ComSQLQry.CALCULATE 
          (ParToCalc VarChar(256))     
       Returns Decimal(31, 9)      
       Language SQL                                               
       Specific CALCULATE                                         
       Modifies SQL Data                                          
       Called On NULL Input                                       
       No External Action                                         
       Not Fenced                                                 
                                                                  
       Set Option DbgView = *Source                               
                                                                  
Begin                                                             
   Declare RtnVal     Decimal(31, 9);                             
   Declare DynSQLStmt VarChar(350);                               
                                                                  
   Declare Continue Handler for SQLEXCEPTION                      
           Return -9999999999999,999999999;                       
                                                                  
    If ParToCalc is NULL or ParToCalc = '' Then Return 0;         
    End If;                                                       
                                                                         
    Set DynSQLStmt = 'Values(' concat Trim(ParToCalc) concat ') into ?'; 
                                                                         
    Prepare DynSQL from DynSQLStmt;                                      
    Execute DynSQL using RtnVal;                                         
    Return RtnVal;                                                       
 End;                                                                    
                                                                         
 Comment On  Specific Function CALCULATE                                  
  is 'Calculate String';    

Commit;

 Values(Calculate(' 3 + 7*2'));
 Values(Calculate('Time(''19.00.00'') - Current_Time'));
 Values(Calculate('sin(1,5)'));

--====================================================================================================
-- User Defined Table Function 
--====================================================================================================
-- Calling user Defined Table Functions
------------------------------------------------------------------------------------------------------
 Select * from Table(USERS()) u;;
 Select * from Table(Schemas()) x;;
 Select * from Table(Message_File_Data('QSYS', 'QSQLMSG'));

------------------------------------------------------------------------------------------------------
-- CL Command with OutFile
------------------------------------------------------------------------------------------------------
Create Or Replace Function ComSQLQry.DspBndDir_Fnc
         (ParBnddir    VarChar(10),
          ParBnddirLib VarChar(10)  Default '*LIBL')
       Returns Table (BndDirLib  Char(10),
                      BndDir     Char(10),
                      ObjLib     Char(10),
                      ObjName    Char(10),
                      ObjType    Char(10),
                      Activation Char(10))   
       Language SQL
       Specific DspBnddir
       Not Deterministic
       Modifies SQL Data
       Called On NULL Input
       Disallow Parallel
       Not Fenced
       
       Set Option DBGVIEW = *Source
 Begin
    -- Create OutFile
    Call QSys2.QCMDEXC(
               'DSPBNDDIR BNDDIR(' concat Trim(ParBnddirLib) concat
                                          '/'                concat 
                                          Trim(ParBndDir)    concat
                                          ') '               concat
                        ' OUTPUT(*OUTFILE) '                 concat
                        ' OUTFILE(QTEMP/TMPBNDDIR)');
                            
     -- Return Result
     Return Select BNDRLB, BNDRNM, BNOLNM, BNOBNM, BNOBTP, BNOACT
              From TMPBNDDIR;                       
 End;   
 
 Commit;
 
 Select * 
   From Table(DspBndDir_Fnc('BXBNDDIR', 'BXOBJ'));
--====================================================================================================
-- Materialized Query Tables (MQT)
--====================================================================================================
-- MQT: Sales per Month
------------------------------------------------------------------------------------------------------
Create Or Replace Table ComSQLQry.SalesMon 
       as (Select Year(Salesdate) As SalesYear,
                 Month(Salesdate) As SalesMonth,
                 Custno,
                 Sum(Amount)      As AmountYear
               From ComSQLQry.Sales
               Group By Year(SalesDate), Month(SalesDate), Custno)
   Data Initially Immediate
   Refresh Deferred
   Maintained By User
   Enable Query Optimization
   Rcdfmt Salesmon;
   
Commit;

Select * from SalesMon;   

------------------------------------------------------------------------------------------------------
-- Sales per year joined with the address master table
------------------------------------------------------------------------------------------------------
Create Or Replace Table ComSQLQry/SalesYear 
       As (Select Year(Salesdate) As Salesyear,
                  A.Custno,       Custname1,     Country,
                  Zipcode,        City,          Street,
                  Sum(Amount)     As AmountYear
               From    ComSQLQry.Sales S Join ComSQLQry.Addressx A
                    On S.CustNo = A.CustNo
               Group By Year(Salesdate), A.Custno, Custname1, Country,
                        Zipcode,         City,     Street)
   Data Initially Immediate
   Refresh Deferred
   Maintained By User
   Enable Query Optimization
   Rcdfmt Salesyear; 
  
Commit;

Select * from SalesYear;  

--****************************************************************************************************
-- Check Constraints
--****************************************************************************************************
-- Attention: Examples Only
Alter Table COMDBMOD.ZZ_ORDER_HEADER
   Add Constraint COMDBMOD.ZZORDHP_ORDER_TYPE_00001 
       Check(Order_Type in ('DO', 'EX', 'UO'))
   Add Constraint COMDBMOD.ZZORDHP_DELIVERY_TERMS_00001
       Check(DELIVERY_TERMS in ('CPT', 'EXW'))  
   Add Constraint COMDBMOD.ZZORDHP_ORDER_HEADER_STATUS_00001
       Check(Order_Header_Status in ('EN', 'OPN', 'CP', 'PD', 'CL', 'FIN'))       ;      

Alter Table COMDBMOD.ZZ_ORDER_DETAIL
   Add Constraint COMDBMOD.ZZORDDP_DELIVERY_QUANTITY_00001 
       Check(DELIVERY_QUANTITY >= 0)
       
   Add Constraint COMDBMOD.ZZORDDP_ORDER_POSITION_STATUS_00001 
       Check(ORDER_POSITION_STATUS In ('EN', 'PD', 'CP', 'CL'))
       
   Add Constraint COMDBMOD.ZZORDDP_ORDER_QUANTITY_00001 
       Check(ORDER_QUANTITY > 0 and ORDER_QUANTITY >= DELIVERY_QUANTITY);

--****************************************************************************************************
-- Referential Integrities
--****************************************************************************************************
-- Attention: Examples Only
-- 1. Order Header Table
Alter Table COMDBMOD.ZZ_ORDER_HEADER
   Add Foreign Key (OHADID)
               References ZZ_ADDRESS_MASTER (ADID) 
       On Delete Restrict 
       On Update Restrict;
       
-- 2. Order Detail Table
Alter Table COMDBMOD.ZZ_ORDER_DETAIL
   Add Foreign Key (ODITID)
               References ZZ_ITEM_MASTER (ITID) 
               On Delete Restrict 
               On Update Restrict;

-- Attention: Order Header without Order Positions and vice versa --> Referential Integrity cannot implemented
Alter Table COMDBMOD.ZZ_ORDER_DETAIL               
   Add Foreign Key (ODOHID)
               References COMDBMOD/ZZ_ORDER_HEADER (OHID) 
               On Delete Restrict 
               On Update Restrict;    

--****************************************************************************************************
-- Trigger
--****************************************************************************************************  
-- Add a Before Insert Trigger that automatically updates the new date column
------------------------------------------------------------------------------------------------------
-- Attention: Example Only
Drop Trigger COMDBMOD.ORDER_HEADER_B4IU_DELDATE; 

Create Or Replace Trigger COMDBMOD.ORDER_HEADER_B4IU_DELDATE 
          Before Insert Or Update Of DELIVERY_DATE_NUM, DELIVERY_DATE 
          On COMDBMOD.ZZ_ORDER_HEADER
          Referencing New as N 
                      Old as O
          For Each Row 
          Mode DB2ROW 
          Program Name ZZOHBIU01 
          Not SECURED
    Begin Atomic
          Declare LocType    VarChar(10) Default '';
          Declare Continue Handler for SQLEXCEPTION Set N.Delivery_Date = '8888-12-31';

          If    Inserting and N.Delivery_Date > '0001-01-01'
             or Updating  and O.Delivery_Date <> N.Delivery_Date
             Then Set N.Delivery_Date_Num = Dec(N.Delivery_Date, 8, 0);  
          ElseIf Inserting and N.Delivery_Date_Num > 10101
             or Updating and O.Delivery_Date_Num <> N.Delivery_Date_Num
             Then Set N.Delivery_Date = Date(Digits(N.Delivery_Date_Num) concat '000000');
          ElseIf Inserting 
             Then Set N.Delivery_Date     = '0001-01-01';
                  Set N.Delivery_Date_Num = 0;   
          End If;
    End;

Commit;
Rollback;
--====================================================================================================
-- Instead Of Trigger
--====================================================================================================
Drop Table if exists ComSQLQry.Itembas;
Drop Table if exists ComSQLQry.ItemDet;
Commit;

Create Or Replace Table ComSQLQry.ItemBas (
         ItemNo       Integer                Not NULL Default 0,
         Description  Varchar(50) CCSID 1141 Not NULL Default '',
         Status       Char(2)     CCSID 1141 Not NULL Default 'A',
         CrtTimeStmp  Timestamp              Not NULL Default Current_Timestamp,
         UpdTimeStmp  Timestamp              Not NULL Default '0001-01-01-00.00.00.000000')
   Rcdfmt ItemBas;

Create Or Replace Table ComSQLQry.ItemDet (
         ItemNo       Integer                 Not NULL Default 0,
         Color        Char(20)    CCSID 1141  Not NULL Default '',
         Material     Varchar(50) CCSID 1141  Not NULL Default '',
         Height       Decimal(5, 3)           Not NULL Default 0,
         Width        Decimal(5, 3)           Not NULL Default 0,
         Depth        Decimal(5, 3)           Not NULL Default 0,
         "COMMENT"    Varchar(256) CCSID 1141 Not NULL Default '',
         CrtTimeStmp  Timestamp               Not NULL Default Current_Timestamp,
         UpdTimeStmp  Timestamp               Not NULL Default '0001-01-01-00.00.00.000000')
   Rcdfmt ItemDet; 
  
Create Or Replace View ComSQLQry.ItemBV01
       (ItemNo, Description, Status,  Color,   Material,
        Height, Width,      Depth,   Comment, Volume)
    as Select b.ItemNo, Description, Status, Color,   Material,
              Height,   Width,       Depth,  Comment,     
              Cast(Height * Width * Depth as Dec(15, 9))
          From ItemBas b Left Outer Join ItemDet d on b.ItemNo = d.ItemNo;    

commit;  

Insert into ItemBV01 (ItemNo, Description)
Values(100, 'Rocker Granpa Hugo');

Insert Into ItemBV01 (ItemNo, Description, Color, Comment)
Values(200, 'Couch Aunt Trude', 'Antic Pink', 'Ultra Soft');

Select * from ItemBV01;

Create Or Replace Trigger ComSQLQry.InsertItem
       Instead Of Insert On ComSQLQry.ItemBV01
       Referencing Old As O 
                   New As N 
      For Each Row 
      Mode DB2SQL
      
      Set Option DbgView=*Source
   
   Begin Atomic
      Declare Isfound Smallint Default 0;
      
      Insert Into ComSQLQry.Itembas (Itemno, Description, Status, CrtTimestmp, UpdTimestmp)
            Values( N.Itemno, N.Description, Case When N.Status = ' ' Or N.Status Is Null 
                                                  Then 'A'
                                                  Else N.Status
                                             End, 
                   Default, Default);
      
      If    N.Color    Is Not Null
         Or N.Material Is Not Null
         Or N.Height   Is Not Null
         Or N.Width    Is Not Null
         Or N.Depth    Is Not Null
         Or N.Comment  Is Not Null 
         Then  Insert Into ComSQLQry.ItemDet 
                     (Itemno,    Color, Material, Height, Width, Depth, Comment, CrtTimestmp, UpdTimestmp)
               Values(N.Itemno,  Coalesce(N.Color,    'Colored'),
                                 Coalesce(N.Material, 'Various'),
                                 Coalesce(N.Height,   0),
                                 Coalesce(N.Width,    0),
                                 Coalesce(N.Depth,    0),
                                 Coalesce(N.Comment,   ''),
                      Default, Default);
      End If;
End;

Commit;