CL:CHGLIBL LIBL(QTEMP COMSQLQRY SYSTOOLS QGPL);
CL:CHGJOB LANGID(DEU) CNTRYID(DE) CCSID(1141);

Select * from Library_List_Info;

--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
-- Group By - Multi-dimensional Grouping, ListAgg and Split
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

--****************************************************************************************************
-- GROUP BY 
--**************************************************************************************************
Select  * from Sales;

Select CustNo, 
       Sum(Amount) as TotalSales, Cast(Avg(Amount) as Dec(11, 3)) as AvgSales
  From Sales
  
  -- Where SalesDate between '2009-01-01' and '2009-12-31'
  Group By CustNo
  Order By CustNo
  ;

Select Year(SalesDate) as SalesYear , CustNo,
       Sum(Amount)     as Total,      Count(*) as Positions
   From Sales
  -- Where SalesDate between '2009-01-01' and '2009-12-31'   
   Group By Year(SalesDate), CustNo
   Order By SalesYear, CustNo;

-- Select Year(SalesDate) as SalesYear , CustNo,
--        Sum(Amount)     as Total,      Count(*) as Positions
--    From Sales
--    Group By SalesYear, CustNo
--    Order By SalesYear, CustNo;   
-- 
-- Multiple Aggregate Functions / Grouping Expression / Where condition / Different sort sequence  
Select Year(SalesDate) SalesYear, CustNo, 
       Count(*)        "NbrRows", Sum(Amount) "Total" ,  
       Cast(Avg(Amount) as Dec(11, 2))        "Average", 
       Min(Amount)     "Minimum", Max(Amount) "Maximum"
   From Sales
   Where ItemNo Between '5100' and '5300'
   Group By Year(SalesDate), CustNo
   Order By CustNO, SalesYear;         
--****************************************************************************************************
-- Multidimensional Grouping 
--****************************************************************************************************
-- RollUp
--====================================================================================================
Select * from Sales;

-- Sales / Year and Customer
Select Year(SalesDate) SalesYear, CustNo, Sum(Amount) Total
   From Sales
   Where CustNo In ('10001', '10003')
   Group By Year(SalesDate), CustNo
   Order By SalesYear, CustNo
;

Select Year(SalesDate) SalesYear, CustNo, Sum(Amount) Total
   From Sales
   Where CustNo In ('10001', '10003')
   Group By RollUp(Year(SalesDate), CustNo)
   Order By SalesYear, CustNo
;

-- Sales / Year/Month/Day
----------------------------------
select Year(SalesDate) SalesYear, Month(SalesDate) SalesMonth, 
       Day(SalesDate) SalesDay,   Sum(Amount) Total
From Sales
Where salesDate between '2008-10-01' and '2009-03-31'
Group By Year(SalesDate), Month(SalesDate), Day(SalesDate)
Order By SalesYear, SalesMonth, SalesDay
;

select Year(SalesDate) SalesYear, Month(SalesDate) SalesMonth, 
       Day(SalesDate) SalesDay,   Sum(Amount) Total
From Sales
Where salesDate between '2008-10-01' and '2009-03-31'
Group By RollUp(Year(SalesDate), Month(SalesDate), Day(SalesDate))
Order By SalesYear, SalesMonth, SalesDay
;

--====================================================================================================
-- Cube
--====================================================================================================
select Year(SalesDate) SalesYear, CustNo, Item, Sum(Amount) Total
From Sales
where     SalesDate between '2008-10-01' and '2009-03-31'
      and CustNo in ('10001', '10003')
Group By Year(SalesDate), CustNo, Item
Order By SalesYear, CustNo, Item
;

select Year(SalesDate) SalesYear, CustNo, Item, Sum(Amount) Total
From Sales
where     SalesDate between '2008-10-01' and '2009-03-31'
      and CustNo in ('10001', '10003')
Group By Cube(Year(SalesDate), CustNo, Item)
Order By SalesYear, CustNo, Item
;

--====================================================================================================
-- Grouping Sets
--====================================================================================================
Select * from Sales;
Select * from AddressX;

Select Year(SalesDate) SalesYear, CustNo, Sum(Amount) Total
   From Sales
   Where CustNo In ('10001', '10003')
   Group By Year(SalesDate), CustNo
   Order By SalesYear, CustNo
;

-- Join without Grouping Sets
---------------------------------------
Select Year(SalesDate) SalesYear, s.CustNo, CustName1, Sum(Amount) Total
   From Sales s join Addressx a on s.CustNo = a.CustNo
   Where s.CustNo In ('10001', '10003')
   Group By Year(SalesDate), s.CustNo, CustName1
   -- Group By Rollup(Year(SalesDate), s.CustNo, CustName1)
   Order By SalesYear, CustNo
;

-- Join with Grouping Sets
--------------------------------------
Select Year(SalesDate) SalesYear, s.CustNo, CustName1, Sum(Amount) Total
   From Sales s join Addressx a on s.CustNo = a.CustNo
   Where s.CustNo In ('10001', '10003')
   Group By Grouping Sets((Year(SalesDate), s.CustNo, CustName1), Year(SalesDate), ())   
   -- Group By Grouping Sets((Year(SalesDate), s.CustNo, CustName1), RollUp(Year(SalesDate)))
   Order By SalesYear, CustNo
;

-- Different Totals
-----------------------
 select Year(SalesDate) SalesYear, CustNo, Item, Sum(Amount) Total
 From Sales
 Where CustNo between '10001' and '10003'
 Group By Grouping Sets((Year(SalesDate), CustNo),
                        (Year(SalesDate), Item), 
                        (Year(SalesDate)), ())
 Order By SalesYear, CustNo, Item
;

 select Year(SalesDate) SalesYear, s.CustNo, a.CustName1, Item, Sum(Amount) Total
 From Sales s Join Addressx a on s.CustNo = a.CustNo
 Where s.CustNo between '10001' and '10003'
 Group By Grouping Sets((Year(SalesDate), s.CustNo, a.CustName1),
                        (Year(SalesDate), Item), 
                        (Year(SalesDate)), ())
 Order By SalesYear, CustNo, Item
;

--====================================================================================================
-- Aggregate Function Grouping
--====================================================================================================
Select * from SalesV01
Order By SalesYear, custNo, ItemNo;

Select SalesYear, CustNo, Sum(Total) Total, 
       Grouping(SalesYear) Grouping_SalesYear, Grouping(CustNo) Grouping_CustNo
  from SalesV01
  Group By RollUp(SalesYear, CustNo)
  Order By SalesYear, custNo;

-- Summary Textes
-------------------------  
Select Case When Grouping(SalesYear) = 1 Then 'Grand Total' 
            When Grouping(CustNo)    = 1 Then 'Total Year ' concat SalesYear 
            Else '' End SummaryText,
       SalesYear, CustNo, Sum(Total) Total, 
       Grouping(SalesYear) Grouping_SalesYear, Grouping(CustNo) Grouping_CustNo
  from SalesV01
  Group By RollUp(SalesYear, CustNo)
  Order By SalesYear, custNo;

-- Summary Textes and Having Clause
-----------------------------------------  
Select Case When     Grouping(SalesYear) = 1 
                 and Grouping(CustNo)    = 1
            Then 'Grand Total'
            When Grouping(CustNo)        = 1 
            Then 'Total Year ' concat SalesYear
            When Grouping(SalesYear)     = 1
            Then 'Total Customer ' concat CustNo
            Else '' End,      
      SalesYear, CustNo, Sum(Total) as Total
   from SalesV01
   Group By Cube(SalesYear, CustNo)
--   Having     Grouping(CustNo)    >= 0
--          and Grouping(SalesYear) >= 0
   Order By SalesYear, CustNo;
  

--****************************************************************************************************
-- Case Clauses, Group By and Aggregate Functions
--****************************************************************************************************
Select * from OrderDetX;

-- Display different values depending on a column value 
---------------------------------------------------------------
Select Status, Case Status When 'CP' Then 'Completed'
                           When 'CL' Then 'Cancelled'
                           When 'EN' Then 'Entered'
                           When 'PD' Then 'Partly Delivered'
                           Else 'Unknown Status' End,
       a.*
    From OrderDetX a;                       
    
-- Merge Values from different columns into a single column
----------------------------------------------------------------
Select OrderQty, DelQty, Status,
       Case Status When 'CP' Then DelQty
                   When 'CL' Then OrderQty
                   When 'EN' Then OrderQty
                   When 'PD' Then DelQty
                   Else 0 End as Quantity,
        a.*
   From OrderDetX a;                     

Select OrderQty, DelQty, Status,
       Case When Status in ('CP', 'PD') Then DelQty
            When Status in ('CL', 'EN') Then OrderQty
            Else 0 End as Quantity,
       a.*
   From OrderDetX a;  

--====================================================================================================  
-- Pivot Table / Tableau croisé dynamique
--====================================================================================================
Select * from Sales;

-- Pivot-Table
Drop view HScommon10/MonSum;

-- Create or Replace view ComSQLQry.Monsum as 

Select Trim(CustNo) CustNo, Year(SalesDate) SalesYear,                                                    
    sum(case when Month(SalesDate)=  1 then Amount else 0 end) as Jan,
    sum(case when Month(SalesDate)=  2 then Amount else 0 end) as Feb,
    sum(case when Month(SalesDate)=  3 then Amount else 0 end) as Mar,
    sum(case when Month(SalesDate)=  4 then Amount else 0 end) as Apr,
    sum(case when Month(SalesDate)=  5 then Amount else 0 end) as May,
    sum(case when Month(SalesDate)=  6 then Amount else 0 end) as Jun,
    sum(case when Month(SalesDate)=  7 then Amount else 0 end) as Jul,
    sum(case when Month(SalesDate)=  8 then Amount else 0 end) as Aug,
    sum(case when Month(SalesDate)=  9 then Amount else 0 end) as Sep,
    sum(case when Month(SalesDate)= 10 then Amount else 0 end) as Oct,
    sum(case when Month(SalesDate)= 11 then Amount else 0 end) as Nov,
    sum(case when Month(SalesDate)= 12 then Amount else 0 end) as Dec,
    sum(Amount) as Total                                             
   from  Sales                                           
   group by CustNo, Year(SalesDate)                                                 
  order by CustNo, SalesYear
;

Select Trim(CustNo) CustNo, Year(SalesDate) 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)                                                 
  order by CustNo, SalesYear
;

Commit;

Select * from Monsum
where SalesYear = 2010
Order By SalesYear, CustNo;

Select * from Monsum
where CustNo = '10001'
      and SalesYear in (2008, 2009)
Order By SalesYear, CustNo;

-- Join View with Address Table
-----------------------------------------
Select SalesYear, m.CustNo, Coalesce(Trim(CustName1), '****Missing****') CustName1, 
       Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Total 
from Monsum m Left Join Addressx a on m.CustNo = a.CustNo 
where SalesYear = 2010
Order By SalesYear, m.CustNo;

-- Create View ComSQLQry.Monsum2 as
Select Salesyear, 
       Jan, Jan * 100,00 / Total Perc_Jan, 
       Feb, Feb * 100,00 / Total Perc_Feb, 
       Mar, Mar * 100,00 / Total Perc_Mar, 
       Apr, Apr * 100,00 / Total Perc_Apr, 
       May, May * 100,00 / Total Perc_May, 
       Jun, Jun * 100,00 / Total Perc_Jun, 
       Jul, Jul * 100,00 / Total Perc_Jul, 
       Aug, Aug * 100,00 / Total Perc_Aug, 
       Sep, Sep * 100,00 / Total Perc_Sep, 
       Oct, Oct * 100,00 / Total Perc_Oct, 
       Nov, Nov * 100,00 / Total Perc_Nov, 
       Dec, Dec * 100,00 / Total Perc_Dec, 
       Total,
       a.* 
from Monsum m Left Join Addressx a on m.CustNo = a.CustNo;

Select SalesYear, CustNo, Coalesce(Trim(CustName1), '****MISSING****') CustName1,
       Jan, Perc_Jan, Feb, Perc_Feb, Mar, Perc_Mar, Apr, Perc_Apr, May, Perc_May, Jun, Perc_Jun,
       Jul, Perc_Jul, Aug, Perc_Aug, Sep, Perc_Sep, Oct, Perc_Oct, Nov, Perc_Nov, Dec, Perc_Dec,
       Total 
  from ComSQLQRy.Monsum2 m
  Where CustNo between '10001' and '10003'
  Order By SalesYear, CustNo; 

--====================================================================================================
-- ABC Customer
--====================================================================================================
Select CustNo, Case When Sum(Amount) > 10000 Then 'A'
                    When Sum(Amount) >  4000 Then 'B'
                    Else 'C' End "ABC",
                    
               Case When Sum(Amount) > 10000 
                    then Sum(Amount) Else 0 End  "A Customer",
               Case When Sum(Amount) between 4000 and 10000 
                    Then Sum(Amount) Else 0 End  "B Customer",
               Case When Sum(Amount) < 4000 
                    Then Sum(Amount) Else 0 End  "C Customer"
  From Sales
  Group by CustNo
  Order By Sum(Amount) Desc;  
  
--****************************************************************************************************
-- ListAGG - Aggregate Function
--****************************************************************************************************
Select * from OrderDetx;
Select * from ItemMastX;

-- List all Items separated by a comma and a blank
-----------------------------------------------------------
 Select Company, OrderNo, 
        ListAgg(Trim(ItemNo), ', ') ListItems    
    From OrderDetX
    Group By Company, OrderNo
    Order By Company, OrderNo
    ;

-- List all Items with Description separatd by a comma and a blank    
--------------------------------------------------------------------------
  Select d.Company, d.OrderNo, 
        ListAgg(Trim(d.ItemNo) concat ' ' concat Descript, ', ') ListItems    
    From OrderDetX d join ItemMastx i on     d.Company = i.Company
                                         and d.ItemNo  = i.ItemNo
    Group By d.Company, OrderNo
    Order By Company, OrderNo
    ;   

-- List all items and each item followed by its delivery values. 
-- Item information is separated by a column and a blank 
-----------------------------------------------------------------------   
  Select d.Company, d.OrderNo, 
        ListAgg(Trim(d.ItemNo) concat ' ' concat (DelQty * Price) concat ' Euro', ', ') ListItems    
    From OrderDetX d join ItemMastx i on     d.Company = i.Company
                                         and d.ItemNo  = i.ItemNo
    Group By d.Company, OrderNo
    Order By Company, OrderNo
    ;       

-- List all items and each item followed by its delivery values followed by an Euro sign. 
-- The delivery values are right adjusted with the LPAD function
-- Item information is separated by a column and a blank 
-- Attention: at least one information has to be converted into either UTF-8 or a double byte CCSID (otherwise the Euro sign is not shown correctly
    Select d.Company, d.OrderNo, 
           ListAgg('Item No: '   concat Trim(d.ItemNo)          concat 
                   ' Del.Val.: ' concat LPAD(DelQty * Price, 8) concat 
                   ' Euro', 
                   ' , ') ListItems  
           -- ListAgg(Cast('Item No: ' as VarChar(10) CCSID 1208) concat Trim(d.ItemNo) concat 
           --             ' Del.Val.: '                          concat LPAD(DelQty * Price, 8) concat 
           --             ' €', 
           --        ' , ') ListItems    
    From OrderDetX d join ItemMastx i on     d.Company = i.Company
                                         and d.ItemNo  = i.ItemNo
    Group By d.Company, OrderNo
    Order By Company, OrderNo
    ;       

-- List all items incl. item description followed by its delivery values followed by an Euro sign 
-- With RPAD the Item Description is padded with *BLANKS up to 20 characters   
    Select d.Company, d.OrderNo, 
           -- ListAgg(Cast('Item No: ' as VarChar(10) CCSID 1208)        concat 
           --              Trim(d.ItemNo) concat ' ' concat Descript     concat 
           --              ' Del.Val.: '  Concat LPAD(DelQty * Price, 8) concat ' €', 
           --        ' , ') ListItems    
           ListAgg(Cast('Item No: ' as VarChar(10) CCSID 1208) concat 
                        Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20) concat 
                        ' Del.Val.: '  concat LPAD(DelQty * Price, 8)       concat ' €', 
                   ' , ') ListItems                         
    From OrderDetX d join ItemMastx i on     d.Company = i.Company
                                         and d.ItemNo  = i.ItemNo
    Group By d.Company, OrderNo
    Order By Company, OrderNo
    ;       
    
-- List all items incl. item description followed by its delivery values followed by an Euro sign. 
-- VARCHAR_FORMAT is used for editing the numeric values 
    Select d.Company, d.OrderNo, 
           ListAgg(Cast('Item No: ' as CLOB(10) CCSID 1208) concat 
                   Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20)               concat 
                   ' Del.Val.: '  concat VarChar_Format(DelQty * Price, '99,990.00') concat 
                   ' €', ' , ') ListItemsUS --,
           -- ListAgg(Cast('Item No: ' as CLOB(10) CCSID 1208) concat 
           --        Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20)               concat 
           --        ' Del.Val.: '  concat Translate(VarChar_Format(DelQty * Price, '99,990.00'), '.,', ',.') concat 
           --        ' €', ' , ') ListItemsEur            
    From OrderDetX d join ItemMastx i on     d.Company = i.Company
                                         and d.ItemNo  = i.ItemNo
    Group By d.Company, OrderNo
    Order By Company, OrderNo
    ;        
    
------------------------------------------------------------------------------------------------------
-- LISTAGG - Within Group
------------------------------------------------------------------------------------------------------    
-- List ItemNo / Order
 Select Company, OrderNo, 
        ListAgg(Trim(ItemNo), ', ')
    from OrderDetx
  Group By Company, OrderNo
  Order By Company, OrderNo;   
  
                            
-- List ItemNo / Order
 Select Company, OrderNo, 
        ListAgg(Trim(ItemNo), ', ') within Group (Order By ItemNo) ListItems
    from OrderDetx
  Group By Company, OrderNo
  Order By Company, OrderNo; 
  
-- Create an JSON Array  
 Select Company, OrderNo, JSON_ArrayAgg(Trim(ItemNo) Order By ItemNo),
        ListAgg(Trim(ItemNo), ', ') within Group (Order By ItemNo) ListItems
    from OrderDetx
  Group By Company, OrderNo
  Order By Company, OrderNo;         
  
-- Order By within Group ItemNo 
Declare Global Temporary Table List_Item_Per_Order as ( 
  Select d.Company, d.OrderNo, 
        -- ListAgg(Cast('Item No: ' as CLOB(10) CCSID 1208) concat 
        --         Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20)               concat 
        --         ' Del.Val.: '  concat VarChar_Format(DelQty * Price, '99,990.00') concat 
        --         ' €', ' , ') 
        --         Within Group (Order By d.ItemNo) ListItemsUS --,
        ListAgg(Cast('Item No: ' as CLOB(10) CCSID 1208) concat 
                Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20)               concat 
                ' Del.Val.: '  concat Translate(VarChar_Format(DelQty * Price, '99,990.00'), '.,', ',.') concat 
                ' €', ' , ') 
                Within Group (Order By d.ItemNo) ListItemsEur            
   From OrderDetX d join ItemMastx i on     d.Company = i.Company
                                        and d.ItemNo  = i.ItemNo
   Group By d.Company, OrderNo
   Order By Company, OrderNo
   ) with Data With Replace 
   ;  
   
Select * from List_Item_Per_Order;   
   
-- Order By within Group Delivery Value descending and Item No     
 Select d.Company, d.OrderNo, 
        -- ListAgg(Cast('Item No: ' as CLOB(10) CCSID 1208) concat 
        --         Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20)               concat 
        --         ' Del.Val.: '  concat VarChar_Format(DelQty * Price, '99,990.00') concat 
        --         ' €', ' , ') 
        --         Within Group (Order By DelQty * Price, d.ItemNo Desc) ListItemsUS --,
        ListAgg(Cast('Item No: ' as CLOB(10) CCSID 1208) concat 
                Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20)               concat 
                ' Del.Val.: '  concat Translate(VarChar_Format(DelQty * Price, '99,990.00'), '.,', ',.') concat 
                ' €', ' , ') 
                Within Group (Order By DelQty * Price Desc, d.ItemNo) ListItemsEur            
   From OrderDetX d join ItemMastx i on     d.Company = i.Company
                                        and d.ItemNo  = i.ItemNo
   Group By d.Company, OrderNo
   Order By Company, OrderNo;       
             
------------------------------------------------------------------------------------------------------
-- LISTAGG and Distict
------------------------------------------------------------------------------------------------------         
-- List ItemNo / Customer
 Select * 
   from Sales
   Order By CustNo, ItemNo;

 Select CustNo, 
        ListAgg(Trim(ItemNo), ', ') within Group (Order By ItemNo)
    from Sales
  Group By CustNo;     

-- List Distinct ItemNo / Customer 
 Select CustNo, ItemNo
   from Sales
   Group By CustNo, ItemNo;

 Select Year(SalesDate) SalesYear, CustNo, 
        Count(*) Positions, Count(Distinct ItemNo) NbrItems
   from Sales
   Group By Year(SalesDate), CustNo
   Order By  SalesYear, CustNo;

 Select Year(SalesDate) SalesYear, CustNo, 
        Count(*) Positions, Count(Distinct ItemNo) NbrItems,
        ListAgg(Distinct Trim(ItemNo), ', ') Within Group (Order By ItemNo)
   from Sales
   Group By Year(SalesDate), CustNo
   Order By  SalesYear, CustNo;
   
Select Year(SalesDate) SalesYear, 
       Count(*) Positions,
       Count(Distinct CustNo) "Nbr Customers", ListAgg(Distinct Trim(CustNo), ', ') Within Group(Order By CustNo) "List Customer",
       Count(Distinct ItemNo) "Nbr Items",     ListAgg(Distinct Trim(ItemNo), ', ') Within Group(Order By ItemNo) "List Items",
       Count(Distinct CustNo concat ItemNo) "Nbr Cust/Item", 
       ListAgg(Distinct Trim(CustNo) concat '-' concat Trim(ItemNo), ', ') Within Group(Order By CustNo concat ItemNo) "List Cust/Item"
From Sales
Group By Year(SalesDate);         

-- List All column Names 
----------------------------------- 
Select Table_Schema, Table_Name, System_Table_Schema, System_Table_Name,
       ListAgg(Column_Name, ', ')              
               within Group (Order By Column_Name)        List_Column_Names,
       ListAgg(Trim(System_Column_Name), ', ') 
               within Group (Order By System_Column_Name) List_System_Column_Names,
       ListAgg('(' concat Trim(Column_Name) concat 
                          Case When Trim(Column_Name) <> Trim(System_Column_Name) 
                               Then ' - ' concat   Trim(System_Column_Name) 
                               Else '' End concat 
                ')', ', ') 
                within Group (Order By Ordinal_Position) List_Column_Names_Ordinal       
from SysColumns
Where Table_Schema = 'COMSQLQRY'
Group By Table_Schema, Table_Name, System_Table_Schema, System_TAble_Name; 

------------------------------------------------------------------------------------------------------
-- Overflow!
------------------------------------------------------------------------------------------------------
-- Overflow - Error
-------------------------
Select ListAgg(Distinct Column_Name, ', ')             
               within Group (Order By Column_Name)        List_Column_Names,
       ListAgg(Distinct Trim(System_Column_Name), ', ') 
               within Group (Order By System_Column_Name) List_System_Column_Names,
       ListAgg(Distinct '(' concat Column_Name concat 
                          Case When Column_Name <> Trim(System_Column_Name) 
                               Then ' - ' concat   Trim(System_Column_Name) 
                               Else '' End concat 
                ')', ', ') 
                within Group (Order By Ordinal_Position) List_Column_Names_Ordinal       
from SysColumns
Where Table_Schema = 'COMSQLQRY';     

-- With Overflow - Truncate
----------------------------------
Select ListAgg(Distinct Column_Name, ', ' on Overflow Truncate '...' with Count )             
               within Group (Order By Column_Name)        List_Column_Names
from SysColumns
Where Table_Schema = 'COMSQLQRY';     

-- as LOB
----------------
Select ListAgg(Distinct Clob(Column_Name, 128),  ', ')             
               within Group (Order By Column_Name)        List_Column_Names
from SysColumns
Where Table_Schema = 'COMSQLQRY';     

--****************************************************************************************************
-- Table Function Split() in SYSTOOLS library
--****************************************************************************************************
Select *
  From Table(SysTools.Split('A, B, C, D, E, V, W, X, Y, Z', ', '));

With x (MyList) as (Values('A, B, C, X'), ('V, W, X, Y, Z'))
Select  MyList, a.* 
   from x cross join Table(SysTools.Split(MyList, ', ')) a
Order By MyList, Ordinal_Position;

------------------------------------------------------------------------------------------------------
-- Consume *.csv file from the IFS
------------------------------------------------------------------------------------------------------
-- Read IFS File
Select * from Table(IFS_READ_UTF8('/home/Hauser/Employee1.csv'));

With x as (Select Line_Number, Line
                               -- Cast(Line as VarChar(1024)) Line
              from Table(IFS_READ_UTF8('/home/Hauser/Employee1.csv')))
Select x.*, Ordinal_Position ColKey, Trim(B '"' from Element) as ColInfo
                                     -- Cast(Trim(B '"' from Element) as VarChar(256)) as ColInfo
   from x cross join Table(SysTools.Split(Line, ',')) a
   Order By Line_Number, ColKey;  
  
-- Columns
With x as (Select * from Table(IFS_READ_UTF8('/home/Hauser/Employee1.csv'))),
     y as (Select x.*, Ordinal_Position ColKey,
                  Trim(B '"' from Element) as ColInfo 
             from x cross join Table(SysTools.Split(Line, ',')))
--             from x cross join Table(Split_Short(Line, ',')))
Select Line_Number,
       Case When ColKey = 1 Then ColInfo End EmployeeNo,     
       Case When ColKey = 2 Then ColInfo End Name,
       Case When ColKey = 3 Then ColInfo End FirstName,
       Case When ColKey = 4 Then ColInfo End Address,
       Case When ColKey = 5 Then ColInfo End ZipCode,
       Case When ColKey = 6 Then ColInfo End City,
       Case When ColKey = 7 Then ColInfo End Country
From y
Where Line_Number > 1;
 
-- Table from IFS File
-- Create or Replace View COMSQLQRY.Emplcsvv01 as
With x as (Select * from Table(IFS_READ_UTF8('/home/Hauser/Employee1.csv'))),
     y as (Select x.*, Ordinal_Position ColKey,
                  Trim(B '"' from Element) as ColInfo 
--             from x cross join Table(SysTools.Split(Line, ',')))
             from x cross join Table(Split_Short(Line, ',')))
Select Line_Number,
       Min(Case When ColKey = 1 Then ColInfo End) EmployeeNo,     
       Min(Case When ColKey = 2 Then ColInfo End) Name,
       Min(Case When ColKey = 3 Then ColInfo End) FirstName,
       Min(Case When ColKey = 4 Then ColInfo End) Address,
       Min(Case When ColKey = 5 Then ColInfo End) ZipCode,
       Min(Case When ColKey = 6 Then ColInfo End) City,
       Min(Case When ColKey = 7 Then ColInfo End) Country
From y
Where Line_Number > 1
Group By Line_Number
Order By Line_Number;       
;

-- SQL View
--------------------------
-- Create or Replace View COMSQLQRY.Emplcsvv02 as
With x as (Select * from Table(IFS_READ_UTF8('/home/Hauser/Employee1.csv'))),
     y as (Select x.*, Ordinal_Position ColKey,
                  Trim(B '"' from Element) as ColInfo 
--             from x cross join Table(SysTools.Split(Line, ',')))
             from x cross join Table(Split_Short(Line, ',')))
Select Line_Number,
       Min(Case When ColKey = 1 Then ColInfo End) EmployeeNo,     
       Min(Case When ColKey = 2 Then ColInfo End) Name,
       Min(Case When ColKey = 3 Then ColInfo End) FirstName,
       Min(Case When ColKey = 4 Then ColInfo End) Address,
       Min(Case When ColKey = 5 Then ColInfo End) ZipCode,
       Min(Case When ColKey = 6 Then ColInfo End) City,
       Min(Case When ColKey = 7 Then ColInfo End) Country
From y
Where Line_Number > 1
Group By Line_Number
Order By Line_Number
;  

Commit;     

-- With Individual Split Function
Select * from Emplcsvv02;
Commit;  

------------------------------------------------------------------------------------------------------
-- Decompose Temporary TAble LIST_ITEM_PER_OWNER
------------------------------------------------------------------------------------------------------
Select x.*
  from List_Item_Per_Order x  ;

-- Decompose Temporary Table LIST_ITEM_PER_ORDER
With x as (Select a.*, Substr(ListItemsEur, 1, Length(ListItemsEur) - 4) ListItemsEurX
             from List_Item_Per_Order a
      --     Where OrderNo = 'BNR2009-10-15/1'
             ),
            
     y as (Select x.*, Ordinal_Position ColKey,
                  RTrim(LTrim(Element, 'Item No: '), ' , ') as ColInfo 
             from x cross join Table(SysTools.Split(ListItemsEurX, ' € , Item No:'))),
     z as (Select y.*, Ordinal_Position, Element, 
                       Cast(Case When Ordinal_Position = 1 Then Element End as VarChar(50)) ItemInfo,
                       Cast(Case When Ordinal_Position = 2 Then
                       Replace(Trim(Element), '.', '') End as VarChar(15)) DelVal
              from y cross join Table(SysTools.Split(ColInfo, 'Del.Val.: '))) 
-- Select * from z;                          
Select Company, OrderNo, 
       Min(Left(ItemInfo,   Locate(' ', ItemInfo)))     ItemNo, 
       Min(Substr(ItemInfo, Locate(' ', ItemInfo) + 1)) ItemDescr,
       Try_Cast(Min(DelVal) as Dec(11, 2)) DelVal
  from z
  Group By Company, OrderNo, ColInfo
  Order By Company, OrderNo, ItemNo
  ;              

------------------------------------------------------------------------------------
-- Sales
------------------------------------------------------------------------------------
With x as (Select * from Table(IFS_READ_UTF8('/home/Hauser/Sales2013.csv'))),
     y as (Select x.*, Ordinal_Position ColKey,
                  Trim(B '"' from Element) as ColInfo 
--             from x cross join Table(SysTools.Split(Line, ';')))
             from x cross join Table(Split_Short(Line, ';')))
  
Select Line_Number,
       Min(Case When ColKey = 1 Then Trim(ColInfo) End) CustNo,     
       Min(Case When ColKey = 2 Then Trim(ColInfo) End) ItemNo,
       Min(Case When ColKey = 3 Then Trim(ColInfo) End) Descript,
       Min(Case When ColKey = 4 Then Date(ColInfo) End) SalesDate,
       Min(Case When ColKey = 5 Then Dec(ColInfo, 11, 2) End) Amount
From y
Where Line_Number > 1
Group By Line_Number
-- Order By Line_Number
;

-- Global Variable
------------------------
Create Or Replace Variable COMSQLQRY.GblIFSFile VarChar(1024) Default '/home/Hauser/Sales2013.csv';

-- Create Or Replace View COMSQLQRY.SALESIFSV01 as 
With x as (Select * from Table(IFS_READ_UTF8(GblIFSFile))),
     y as (Select x.*, Ordinal_Position ColKey,
                  Trim(B '"' from Element) as ColInfo 
   --          from x cross join Table(SysTools.Split(Line, ';')))
             from x cross join Table(Split_Short(Line, ';')))
Select Line_Number,
       Min(Case When ColKey = 1 Then Trim(ColInfo) End) CustNo,     
       Min(Case When ColKey = 2 Then Trim(ColInfo) End) ItemNo,
       Min(Case When ColKey = 3 Then Trim(ColInfo) End) Descript,
       Min(Case When ColKey = 4 Then Date(ColInfo) End) SalesDate,
       Min(Case When ColKey = 5 Then Dec(ColInfo, 11, 2) End) Amount
From y
Where Line_Number > 1
Group By Line_Number;
-- Order By Line_Number
;
Commit;

Set GblIFSFile = '/home/Hauser/Sales2014.csv';
Set GblIFSFile = '/home/Hauser/Sales2013.csv';
Select * from SALESIFSV01;

Set GblIFSFile = '/home/Hauser/Sales2015.csv';

Select s.CustNo, CustName1, City, Sum(Amount) Total 
   from SALESIFSV01 s join AddressX a on s.CustNo = a.CustNo
   Group By s.CustNo, CustName1, City
   Order By s.Custno;
   ;