Select * from Library_List_Info;

CL:CHGLIBL LIBL(QTEMP HSCOMMOND2 SYSTOOLS QGPL);

-- CL:ADDLIBLE LIB(HSCOMMOND2) POSITION(*AFTER QTEMP);

Select * from lobstaff;
Select * from SalesV05; -- View Sales per Month - Pivot

--*********************************************************************************************************************
-- JSON Publishing Functions
--*********************************************************************************************************************
-- Objects based on Single Columns
-------------------------------------
Select * from LobStaff;

Select JSON_OBJECT(Key 'EmployeeNo' Value EmployeeNo)            "Int.EmployeeNo",
       JSON_OBJECT(Key 'LastName'   Value Name)                  "VarChar Name",
       JSON_OBJECT(Key 'Firstname'  Value NullIf(Firstname, '')) "With NULL Value",
       JSON_Object(Key 'City'       Value(City) 
                   Returning VarChar(50) CCSID 1208 normalized)  "Cvt.to VarChar" 
  From LOBSTAFF;     

-- Syntax Alternative
Select JSON_OBJECT('EmployeeNo' : EmployeeNo)                    "Int.EmployeeNo",
       JSON_OBJECT('LastName'   : Name)                          "VarChar Name",
       JSON_OBJECT('Firstname'  : NullIf(Firstname, ''))         "With NULL-Value",
       JSON_Object('City'       : City 
                   Returning VarChar(50) CCSID 1208 normalized)  "Cvt.VarChar" 
  From LOBSTAFF;     


-- (Nested) Objects based on multiple columns
-----------------------------------------------
-- Object based on multiple Columns
Select JSON_Object(Key 'ZipCodeCity' 
            Value Trim(ZipCode) concat ' ' concat Trim(City))  as "ZipCode City"
>From LOBStaff;

Select JSON_Object('ZipCodeCity': 
                   Trim(ZipCode) concat ' ' concat Trim(City)) as "ZipCode City"
   From LOBStaff;

-- Nested Objects 
Select JSON_Object(Key 'Address'     
             Value JSON_Object(Key 'ZipCode' Value ZipCode,
                               Key 'Town'    Value City),
                   Key 'Street'  Value Address)                as "Nested Address"
   From LOBStaff;

Select JSON_Object('Address':     
                    JSON_Object('ZipCode' : ZipCode,
                                'Town'    : City),
                   'Street': Address)                          as "Nested Address"              
   From LOBStaff;

-- Nested Object with Absent on NULL  
Select JSON_Object('Employee':
                   JSON_Object('EmployeeNo' : EmployeeNo,
                               'Firstname'  : NullIf(Firstname, '') ,
                               'Lastname'   : Name 
                               Absent on NULL))                as "With Absent"                 
   From LOBStaff;


-- Format JSON 
----------------------------------*
Values(Json_Object('Literal true': 'true', 'Literal false': 'false'));
-- Values(Json_Object('Literal true': true, 'Literal false': false));

-- Returning JSON literals
Select x.*, JSON_Object('Time'       : OpnTime,
                        'Open/Close' : case OpnCls When '0' Then 'false'
                                                   When '1' Then 'true'
                                       End,
                        'Open'       : OpnCls)
  from (Values('08:00:00', '0'), ('09:00:00', '1'), 
              ('10:00:00', '1'), ('11:00:00', '0')) x (OpnTime, OpnCls);

-- With Format JSON              
Select x.*, JSON_Object('Time'       : OpnTime,
                        'Open/Close' : case OpnCls When '0' Then 'false'
                                                   When '1' Then 'true'
                                       End  Format JSON ,
                        'Open'       : OpnCls)
  from (Values('08:00:00', '0'), ('09:00:00', '1'), 
              ('10:00:00', '1'), ('11:00:00', '0')) x (OpnTime, OpnCls);              


--------------------------------------------------------------------------------------------------------------
-- Common Table Expressions
--------------------------------------------------------------------------------------------------------------
-- X CTE: Determine the total amount per customer                 
-- Y CTE: Accumulate the total over all customers                 
-- Final: Display all columns from the sales table,              
--        add a column that includes the total per customer       
--        add a column that includes the total over all customers    
--------------------------------------------------------------------------------------------------------------
with x as (select CustNo as xCustNo, sum(Amount) as TotalCust 
             from Sales
             group by CustNo),                             
     y as (Select sum(Amount) as Total from Sales)           
select a.*, TotalCust, Total                             
   from Sales a join x on CustNo = xCustNo                 
                 cross join y; 

-- Determine the Sales within a specific date range
----------------------------------------------------------------------------------
with a as (Select * from Sales
             where SalesDate between '2009-01-01' and '2009-12-31'),
     x as (select CustNo as xCustNo, sum(Amount) as TotalCust 
             from a
             group by CustNo),                             
     y as (Select sum(Amount) as Total from a)           
select a.*, TotalCust, Total                                 
   from a join x on CustNo = xCustNo                 
                 cross join y;
                 
-- JSON Objects and Common Table Expressions
With x as (Select JSON_Object('Name'       : Trim(Name),
                              'FirstName'  : Trim(FirstName) ) ObjName,
                  JSON_Object('Town'       : Trim(City),
                              'PostalCode' : Trim(ZipCode),
                              'Country'    : Trim(Country),
                              'Street'     : Trim(Address)) ObjAddress, 
                   a.*
             From LobStaff a),
     y as (Select JSON_Object('EmplNo'   : EmployeeNo,
                              'Name'     : ObjName    ,
                              'Address'  : ObjAddress ) ObjEmpl,
                  x.*
             From x)        
Select JSON_Object('Employee': ObjEmpl)
   from y; 

-- JSON Objects and Common Table Expressions
-- with Format JSON   
With x as (Select JSON_Object('Name'       : Trim(Name),
                              'FirstName'  : Trim(FirstName) ) ObjName,
                  JSON_Object('Town'       : Trim(City),
                              'PostalCode' : Trim(ZipCode),
                              'Country'    : Trim(Country),
                              'Street'     : Trim(Address)) ObjAddress, 
                   a.*
             From LobStaff a),
     y as (Select JSON_Object('EmplNo'   : EmployeeNo,
                              'Name'     : ObjName     Format JSON,
                              'Address'  : ObjAddress  Format JSON) ObjEmpl,
                  x.*
             From x)
-- Select * from y;             
Select JSON_Object('Employee': ObjEmpl Format JSON)
   from y; 
   
                           
-----------------------------------------------------------------------------------------------------------------------
-- JSON_ARRAY
-----------------------------------------------------------------------------------------------------------------------
Select a.* from SalesV05 a
  Where SalesYear = 2014
  ;
  
-- Array Sales per Month
-------------------------------*
-- List Values located within the same row
-----------------------------------------------*
Select JSON_Array(Jan, Feb, Mar , Apr, May, Jun, 
                  Jul, Aug, Sep, Oct, Nov, Dec ) as "ArrSalesMon"      
  From SalesV05
  Where SalesYear = 2014;

-- List quarterly Sales 
Select JSON_ARRAY(Jan + Feb + Mar, 
                  Apr + May + Jun,
                  Jul + Aug + Sep,
                  Oct + Nov + Dec) as "ArrSalesQuarter"
  From SalesV05
  Where SalesYear = 2014;

 
-- Object including an Array 
Select JSON_Object(Key 'Customer'    Value Trim(CustNo),
                   Key 'SalesYear'   Value SalesYear,
                   Key 'SalesMonth'
                   Value JSON_Array(Jan, Feb, Mar, Apr, May, Jun, 
                                    Jul, Aug, Sep, Oct, Nov, Dec)) as "ArrSalesMon"      
  From SalesV05
  Where SalesYear = 2014;  
  
Select JSON_Object('Customer'  : Trim(CustNo),
                   'SalesYear' : SalesYear,
                   'SalesMonth':
                   JSON_Array(Jan, Feb, Mar, Apr, May, Jun, 
                              Jul, Aug, Sep, Oct, Nov, Dec)) as "ArrSalesMon"      
  From SalesV05
  Where SalesYear = 2014;  

-- NULL Values
-----------------------*
Select * from SalesV05
Where SalesYear = 2014;


Select * from SalesV06
Where SalesYear = 2014;

Select JSON_Array(Jan, Feb, Mar , Apr, May, Jun, 
                  Jul, Aug, Sep, Oct, Nov, Dec ) as "ArrSalesMon"      
  From SalesV06
--  From SalesV05
  Where SalesYear = 2014;
  
Select JSON_Array(Jan, Feb, Mar , Apr, May, Jun, 
                  Jul, Aug, Sep, Oct, Nov, Dec 
                  NULL on NULL)                  as "ArrSalesMon"      
  From SalesV06
  Where SalesYear = 2014;  

------------------------------------------------------------------------------------------------------------------------
-- JSON_ARRAYAGG
------------------------------------------------------------------------------------------------------------------------
-- Numeric Array
Select JSON_ArrayAgg(EmployeeNo) "Arr EmployeeNo"
  from LobStaff a;

-- Character Array  
Select JSON_ArrayAgg(Trim(Name) concat ' ' concat Trim(FirstName)) "Arr Name"
  from LobStaff a;  

-- Character Array with Order By  
Select distinct City, ZipCode 
  from LobStaff;

With x as (Select Distinct ZipCode, City
             from LobStaff)
Select JSON_ArrayAgg(JSON_Object('City'   : Trim(City),
                                 'ZipCode': Trim(ZipCode)) 
                                 Order By City)
  from x;

-- Array with nested JSON_Objects, Absent on NULL and Order By
Select JSON_ArrayAgg(
            JSON_Object('Employee': EmployeeNo,
                        'Name'    : JSON_Object('Lastname' : Name,
                                                'Firstname': NullIf(FirstName, '')
                                                Absent on NULL) ,
                        'Address' : JSON_Object('City'     : City,
                                                'Country'  : Case When City <> 'Kaufering'
                                                                  Then Country
                                                             End,
                                                'Street'   : Address
                                                Absent on NULL))
                        Order By City Desc, Name)
  from LobStaff a
  Where ZipCode > '80000'
  ;                     

-----------------------------------------------------------------------------------------------------
-- More Complex SQL Data
-----------------------------------------------------------------------------------------------------
-- JSON Data: All Orders in December 2015  
Select * from OrderHdrX;   
Select * from OrderDetx;
Select * from Addressx;  
Select * from ItemMastX;  

With OrderAddr 
-- 1. CTE: Join Order Header and Address Table
--         Select All Orders in December 2015
--         Delivery Date is converted into Character
--         Order Type and Delivery Terms are formatted as needed in the final JSON data
     as (Select h.Company, h.OrderNo, h.CustNo, 
                DelDate,  Char(DelDate, ISO) as DelDateChar,
                OrderType, case OrderType When 'DO' Then 'Domestic'
                                          When 'EX' Then 'Export'
                                          When 'UO' Then 'Express'
                                          Else 'Unknown' End as OrdTypeText, 
                DelTerms,  Case DelTerms  When 'EXW' Then 'ex works'
                                          When 'CPT' Then 'delivered free'
                                          Else 'Unknown' End as DelTermsText,       
                Trim(Trim(CustName1) concat ' ' concat Trim(CustName2)) as CustName,
                Trim(Street) as Street, Trim(ZipCode) as ZipCode, Trim(City) as City
            From OrderHdrx h join AddressX a on h.CustNo = a.CustNo
            Where DelDate between '2015-12-01' and '2015-12-31'),
                     
     --2. CTE: Join OrderAddr CTE with Order Detail and Item Master                
     OrderPos  
     as (Select h.*, 
                OrderPos, Trim(d.ItemNo) as ItemNo,
                OrderQty, DelQty, d.Status as PosStatus,
                Trim(Descript) as Descript, Price, Trim(Currency) as Currency,
                Cast(DelQty * Price as Dec(11, 2)) DelValue
            from OrderAddr h join OrderDetX d on     h.Company = d.Company
                                                 and h.OrderNo = d.OrderNo
                             join ItemMastX i on     d.Company = i.Company
                                                 and d.ItemNo  = i.ItemNo
            Where DelQty > 0), 
                                            
    --3. CTE: Build JSON Object with Customer information
    --        for each customer with orders in December 2017 (based on OrderAddr CTE)                                                                               
     CTEAddress 
     as (Select Distinct CustNo, 
                         JSON_Object('CustomerNo': Trim(CustNo),
                                     'Name'      : CustName, 
                                     'Address'   : 
                                     JSON_Object('Street'     : Street,
                                                 'PostalCode' : ZipCode,
                                                 'City'       : City)) as ObjCust
           From OrderAddr),
                    
     -- 4. Build JSON Object with the Order Detail information
     --    for each Order Position in each order delivered in December 2017 
     --    (based on the ORDERPOS CTE)               
     CTEObjPos 
     as (Select Company, OrderNo, OrderPos,
                JSON_Object('PositionNo': OrderPos,
                            'Item': 
                            JSON_Object('ItemNo':          ItemNo,
                                        'ItemDescription': Descript,
                                        'PricePerUnit':    Price,
                                        'Currency':        Currency),
                            'DeliveryQuantity': DelQty,
                            'DeliveryValue'   : DelValue) ObjPos 
           From OrderPos),
     
     -- 5. Aggregate the JSON Objects for Order Position per Order
     --    Based on the CTEOBJPOS CTE               
     CTEArrPos 
     as (Select Company, OrderNo, 
                JSON_ArrayAgg(ObjPos Format JSON) ArrPos
           From CTEObjPos
           Group By Company, OrderNo),
           
     -- 6. Build the JSON Object with all Order Header, Address and Order Detail Information
     --    for each order delivered in December 2015   
     CTEOrdHdr 
     as (Select JSON_Object('Company':      h.Company,
                            'OrderNo':      h.OrderNo,
                            'CustomerInfo': ObjCust      Format JSON ,
                            'DeliveryDate': DelDateChar,
                            'OrderType':    OrdTypeText,
                            'IncoTerms':    DelTermsText,
                            'Positions':    ArrPos       Format JSON) as ObjOrder
           from OrderAddr h join CTEAddress a on     h.CustNo = a.CustNo
                            join CTEArrPos p  on     h.Company = p.company
                                                 and h.OrderNo = p.OrderNo)
-- Final Select
-- Build an Array with an JSON Object for each order delivered in December 2015
-- Add an Root element
Select JSON_Object('DeliveredOrders': 
                      JSON_Object('Order': JSON_ARRAYAgg(ObjOrder Format JSON)) 
                      Format JSON)
   from CTEOrdHdr;
   
   
--**********************************************************************************************************************************************
-- Part 2: Consuming JSON
--********************************************************************************************************************************************** 
-----------------------------------------------------------------------------------------------------------
-- Test Environment
-----------------------------------------------------------------------------------------------------------
Drop Table MyJSON;
Commit;

Create Table qtemp.MyJSON 
(JSONText VarChar(16000) CCSID 1208 Default '');

Insert into MyJSON
Values('{
	"Id": 1000,
	"Name": {
		"FirstName": "Fritz",
		"LastName": "Fischer"
	},
	"Phone": [{
		"CountryCode": "0049",
		"AreaCode": "08191",
		"PhoneNumber": "239806"
	},
	{
		"CountryCode": "0049",
		"AreaCode": "0721",
		"PhoneNumber": "223777"
	},
	{
		"CountryCode": "0049",
		"AreaCode": "07841",
		"PhoneNumber": "123456"
	},
	{
		"CountryCode": "0049",
		"AreaCode": "06074",
		"PhoneNumber": "50546"
	},
	{
		"CountryCode": "0049",
		"AreaCode": "06023",
		"PhoneNumber": "33445"
	}]
}')
With NC;

Select * from QTEMP.MyJSON;

----------------------------------------------------------------------------------------------
-- Read JSON Documents
----------------------------------------------------------------------------------------------
-- 1.1 Get JSON from Table 
----------------------------------------------*
-- 1.1.1. Column and JSON Names are identical
Select x.*
  from MyJSON cross join 
       JSON_TABLE(JSONTEXT, '$.Name'
                  Columns("FirstName" VarChar(25),
                          "LastName"  VarChar(25))) as x;
Select x.*
  from JSON_TABLE((Select JSONTEXT from MyJSON), '$.Name'
                  Columns("FirstName" VarChar(25),
                          "LastName"  VarChar(25))) as x;

-- 1.1.2. Different Column and JSON Names
 Select x.*
   from JSON_TABLE((Select JSONTEXT from MyJSON), '$.Name'
                   Columns(FirstName VarChar(25) Path '$.FirstName',
                           LastName  VarChar(25) Path '$.LastName')) as x;

-- with lax                          
Select x.*
  from JSON_TABLE((Select JSONTEXT from MyJSON), '$.Name'
                  Columns(FirstName VarChar(25) Path 'lax $.FirstName',
                          LastName  VarChar(25) Path 'lax $.LastName')) as x;                          
   

-- 1.2. Get JSON from IFS File
-------------------------------------*
Values(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'));

Drop table myjsontable;

Declare Global Temporary Table MYJSONTable
       (MyData CLOB(2 M) CCSID 1208);
       
Insert into MyJSONTable 
       Values(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'))
With NC;   

Select * from MyJSONTable;          

Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$.Name'
                  Columns(FirstName VarChar(25) Path '$.FirstName',
                          LastName  VarChar(25) Path '$.LastName')) as x;

Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$.Name'
                  Columns(FirstName VarChar(25) Path 'lax $.FirstName',
                          LastName  VarChar(25) Path 'lax $.LastName')) as x;                          

-- 2. Access Arrays
-------------------------------*
-- 2.1. All Array Elements
-------------------------------------*
-- 2.1.1. Column and JSON Names identical
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$.Phone[*]'
                 -- '$.Phone'
                  Columns("CountryCode" VarChar(25) ,
                          "AreaCode"    VarChar(25) ,
                          "PhoneNumber" VarChar(25) )) as x;

-- 2.1.2. Different Column and JSON Names
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$.Phone[*]'
                  Columns(CountryCode VarChar(25) Path '$.CountryCode',
                          AreaCode    VarChar(25) Path '$.AreaCode',
                          PhoneNumber VarChar(25) Path '$.PhoneNumber')) as x;

-- 2.2. Specific Array Elements (3rd - Element)
----------------------------------------------*
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$.Phone[2]'
                  Columns(CountryCode VarChar(25) Path '$.CountryCode',
                          AreaCode    VarChar(25) Path '$.AreaCode',
                          PhoneNumber VarChar(25) Path '$.PhoneNumber')) as x;

                  
                        
-- 2.3. Last Array Element
------------------------------*                          
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$.Phone[last]'
                  Columns(CountryCode VarChar(25) Path '$.CountryCode',
                          AreaCode    VarChar(25) Path '$.AreaCode',
                          PhoneNumber VarChar(25) Path '$.PhoneNumber')) as x; 

-- 2.4. Third Last Element
-------------------------------*                            
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$.Phone[last - 2]'
                  Columns(CountryCode VarChar(25) Path '$.CountryCode',
                          AreaCode    VarChar(25) Path '$.AreaCode',
                          PhoneNumber VarChar(25) Path '$.PhoneNumber')) as x;     
                                                 
-- 2.5. Range of Elements
---------------------------------*
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$.Phone[1 to 3]'
                  Columns(CountryCode VarChar(25) Path '$.CountryCode',
                          AreaCode    VarChar(25) Path '$.AreaCode',
                          PhoneNumber VarChar(25) Path '$.PhoneNumber')) as x;

-- 2.6. Specific Elemts side by side
----------------------------------------*                          
Select CountryCode1 concat '-' concat AreaCode1 concat '/'concat PhoneNbr1 PhoneNbr1,
       CountryCode2 concat '-' concat AreaCode2 concat '/'concat PhoneNbr2 PhoneNbr2,
       CountryCode3 concat '-' concat AreaCode3 concat '/'concat PhoneNbr3 PhoneNbr3 
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$'
          Columns(CountryCode1 VarChar(25) Path '$.Phone[0].CountryCode',
                  AreaCode1    VarChar(25) Path '$.Phone[0].AreaCode',
                  PhoneNbr1    VarChar(25) Path '$.Phone[0].PhoneNumber',
                  
                  CountryCode2 VarChar(25) Path '$.Phone[1].CountryCode',
                  AreaCode2    VarChar(25) Path '$.Phone[1].AreaCode',
                  PhoneNbr2    VarChar(25) Path '$.Phone[1].PhoneNumber', 
                  
                  CountryCode3 VarChar(25) Path '$.Phone[2].CountryCode',
                  AreaCode3    VarChar(25) Path '$.Phone[2].AreaCode',
                  PhoneNbr3    VarChar(25) Path '$.Phone[2].PhoneNumber')) as x;
                  ;        

-- 3. JSON Nested Column Definition
-------------------------------------*
-- --> NESTED Objects in Arrays

Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$'
          Columns(Id          Integer     Path '$.Id',
                  FirstName   VarChar(25) Path '$.Name.FirstName',
                  LastName    VarChar(25) Path '$.Name.LastName',
                  Nested      '$.Phone[*]'
                      Columns ("CountryCode" VarChar(25) ,
                               "AreaCode"    VarChar(25) ,
                               "PhoneNumber" VarChar(25) ))) as x;
                               
-- 4. JSON Table Ordinality Column Definition
--------------------------------------------------*
-- --> Add columns for Ordinality
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$'
          Columns(OrdId       for Ordinality,
                  Id          Integer     Path '$.Id',
                  FirstName   VarChar(25) Path '$.Name.FirstName',
                  LastName    VarChar(25) Path '$.Name.LastName',
                  Nested      '$.Phone[*]'
                      Columns (OrdPhone    for Ordinality,
                               CountryCode VarChar(25) Path '$.CountryCode',
                               AreaCode    VarChar(25) Path '$.AreaCode',
                               PhoneNumber VarChar(25) Path '$.PhoneNumber'))) as x;


                               
-- 5. JSON Table formatted column definition
------------------------------------------------*
-- --> Return JSON Arrays and/or JSON Objects                               
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON01_NamePhone.json'), 
                 '$'
          Columns(Id          Integer      Path '$.Id',
                  FirstName   VarChar(25)  Path '$.Name.FirstName',
                  LastName    VarChar(25)  Path '$.Name.LastName',
                  JSONName    VarChar(50)  Format JSON
                                           Path '$.Name',
                  JSONPhone   VarChar(256) Format JSON
                                            Path '$.Phone')) as x;


Values(Get_Clob_From_File('/home/Hauser/JSON02_Employee.json'));

-- More complex examples 
----------------------------------*
-- Information from different nested Objects and Arrays
-- Default Values for missing and errorneous information

Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON02_Employee.json'), 
                 '$.Staff.Department[*].Employee[*]'
          Columns(FirstName VarChar(50) Path 'lax $.Name.FirstName',
                  LastName  VarChar(50) Path 'lax $.Name.LastName',
                  eMail     VarChar(80) Path 'lax $.Privacy.eMail',
                  BirthDay  Date        Path 'lax $.Privacy.Birthday'
                                        Default '0001-01-01' on Empty
                                        Default '9999-12-31' on Error,
                  Salary    Dec(11, 2)  Path 'lax $.Privacy.Salary.Salary'
                                        Default     0,00  on Empty
                                        Default -9999,99  on Error,
                  Currency  VarChar(5)  Path 'lax $.Privacy.Salary.Currency'
                                        Default 'EUR' on Empty
                                        Default ''    on Error)) x;
                                        
-- Includes Department on an upper level
-- --> Nested Column Definition required due to the nested Arrays
 
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON02_Employee.json'), 
                 '$.Staff.Department[*]'
          Columns("DeptName" VarChar(50) ,
                  Nested     '$.Employee[*]'
                     Columns( FirstName VarChar(50) Path 'lax $.Name.FirstName',
                              LastName  VarChar(50) Path 'lax $.Name.LastName',
                              eMail     VarChar(80) Path 'lax $.Privacy.eMail',
                              BirthDay  Date        Path 'lax $.Privacy.Birthday'
                                                    Default '0001-01-01' on Empty
                                                    Default '9999-12-31' on Error,
                              Salary    Dec(11, 2)  Path 'lax $.Privacy.Salary.Salary'
                                                    Default 0 on Empty
                                                    Default -9999,99 on Error,
                              Currency  VarChar(5)  Path 'lax $.Privacy.Salary.Currency'
                                                    Default 'EUR' on Empty
                                                    Default '' On Error))) x;

-- 3 Nested Levels: Department / Employee / Phone 
------------------------------------------------------                                                    
Select x.*
  from JSON_TABLE(Get_CLOB_From_File('/home/Hauser/JSON02_Employee.json'), 
                 '$.Staff.Department[*]'
          Columns(OrdDep     for Ordinality,
                  "DeptName" VarChar(50) ,
                  
                  Nested     '$.Employee[*]'
                     Columns( OrdEmployee  for Ordinality,
                              FirstName    VarChar(50)  Path 'lax $.Name.FirstName',
                              LastName     VarChar(50)  Path 'lax $.Name.LastName',
                              eMail        VarChar(80)  Path 'lax $.Privacy.eMail',
                              BirthDay     Date         Path 'lax $.Privacy.Birthday'
                                                        Default '0001-01-01' on Empty
                                                        Default '9999-12-31' on Error,
                                                    
                              Nested       '$.Privacy.Phone[*]'
                                 Columns ( OrdPhone    for Ordinality,
                                           CountryCode VarChar(25) Path '$.CountryCode',
                                           AreaCode    VarChar(25) Path '$.AreaCode',
                                           PhoneNumber VarChar(25) Path '$.PhoneNumber'),
                                          
                              Salary       Dec(11, 2)  Path 'lax $.Privacy.Salary.Salary'
                                                       Default 0 on Empty
                                                       Default -9999,99 on Error,
                              Currency     VarChar(5)  Path 'lax $.Privacy.Salary.Currency'
                                                       Default 'EUR' on Empty
                                                       Default '' On Error))) x;      
                                                       
                                                                                                  
---------------------------------------------------------------------------------------------------
-- Web-Service
---------------------------------------------------------------------------------------------------

-- Weather Forecast
-------------------------------------------------------------------------
--Create or Replace Variable GblKeyAPIXUWeather       
--       Varchar( 50)                                 
--       Default  'YOUR_API_KEY' ; 
Cl:ADDLIBLE LIB(HSRRRPG) POSITION(*LAST);

Values(http_get('http://api.weatherstack.com/current?access_key=' concat Trim(GblKEyAPIXUWeather) concat '&query=Kaufering', ''));
Values(http_get('http://api.weatherstack.com/current?access_key=' concat Trim(GblKEyAPIXUWeather) concat '&query=Kaufering&unit=f', ''));


Select x.*
  from JSON_TABLE(http_get('http://api.weatherstack.com/current?access_key=' concat Trim(GblKEyAPIXUWeather) concat '&query=Kaufering', ''), 
                 '$'
           Columns(City          VarChar(50) Path 'lax $.location.name',      
                   Region        Varchar(50) Path 'lax $.location.region',
                   Country       VarChar(50) Path 'lax $.location.country',
                   Latitude      Dec(7, 2)   Path 'lax $.location.lat',
                   Longitude     Dec(7, 2)   Path 'lax $.location.lon',
                   LocTime       VarChar(26) Path 'lax $.location.localtime',
                   LastUpd       VarChar(26) Path 'lax $.current.observation_time',
                   TempCels      Dec(5, 2)   Path 'lax $.current.temperature'
                                                  Default 999 on Error,
                   WeatherText  VarChar(50)  Path 'lax $.current.weather_descriptions[0]',
                   WindKMH       Dec(5, 2)   Path 'lax $.current.wind_speed'
                                                  Default 999 on Error,
                   WindDir       VarChar(10) Path 'lax $.current.wind_dir',
                   PressureMB    Dec(7, 1)   Path 'lax $.current.pressure',
                   Humidity       Integer    Path 'lax $.current.humidity', 
                   UVIndex        Integer    Path 'lax $.current.uv_index',
                   Visibility     Integer    Path 'lax $.current.visibility',
                   IsDay          VarChar(5) Path 'lax $.current.is_day'
                  )) x;

-- Global Variable for Weather Webservice
---------------------------------------------* 
Create Or Replace Variable HSCOMMOND2.GblWeatherCity         
          VARCHAR(256)  DEFAULT 'Kaufering';

-- Unit: m=metric / f=fahrenheit          
Create Or Replace Variable HSCOMMOND2.GblWeatherUnit         
          VARCHAR(5)  DEFAULT 'm';          
          
Create or Replace Variable HSCOMMOND2.GblWeatherForecastDays 
          Integer   Default 10;

Commit;

Set gblWeatherCity = 'Kaufering';
Set GblWeatherUnit = 'm';

Values(GblWeatherCity);
Values(GblWeatherForecastDays);

-- Create Or Replace View HSCOMMOND2.CurrWeather as
Select City, Region, Country, Latitude, Longitude, 
       Timestamp_Format(LocTime, 'YYYY-MM-DD HH24:MI') as LocTimestamp,
       Time(Timestamp_Format(LastUpd, 'HH12:MI PM'))   as LastUpdTime,
       TempCels, WeatherText, WindKMH, WindDir, PressureMB, Humidity, UVIndex, Visibility, IsDay
  from JSON_TABLE(http_get('http://api.weatherstack.com/current?access_key=' concat Trim(GblKEyAPIXUWeather) concat 
                              '&query=' concat Trim(GblWeatherCity) concat 
                              '&units=' concat Trim(GblWeatherUnit), ''), 
                 '$'
           Columns(City          VarChar(50) Path '$.location.name',      
                   Region        Varchar(50) Path 'lax $.location.region',
                   Country       VarChar(50) Path 'lax $.location.country',
                   Latitude      Dec(7, 2)   Path 'lax $.location.lat',
                   Longitude     Dec(7, 2)   Path 'lax $.location.lon',
                   LocTime       VarChar(26) Path 'lax $.location.localtime',
                   LastUpd       VarChar(26) Path 'lax $.current.observation_time',
                   TempCels      Dec(5, 2)   Path 'lax $.current.temperature'
                                                  Default 999 on Error,
                   WeatherText  VarChar(50)  Path 'lax $.current.weather_descriptions[0]',
                   WindKMH       Dec(5, 2)   Path 'lax $.current.wind_speed'
                                                   Default 999 on Error,
                   WindDir       VarChar(10) Path 'lax $.current.wind_dir',
                   PressureMB    Dec(7, 1)   Path 'lax $.current.pressure',
                   Humidity      Integer     Path 'lax $.current.humidity', 
                   UVIndex       Integer     Path 'lax $.current.uv_index',
                   Visibility    Integer     Path 'lax $.current.visibility',
                   IsDay         VarChar(5)  Path 'lax $.current.is_day'
                  )) x;

Commit;

Select * from HSCOMMOND2.CurrWeather;

Set GblWeatherCity = 'Kaufering';  
Set GblWeatherCity = 'Toronto'; 
set GblWeatherCity = 'New York';
set GblWeatherCity = 'Los Angeles';
           
Select  City, Region, Country, LocTimestamp, LastUpdTime LastUpd, TempCels, WeatherText 
   from HSCOMMOND2.CurrWeather;