SQL Workshop - Remote Workshop with Hands On Exercises

Speaker:
Birgitta Hauser

Tuesday, April 28, 2020 - 10:00am to Wednesday, April 29, 2020 - 12:00pm

If you are not a member, there will be a $60 charge to attend this workshop which will include a QUSER membership good through August 2021. $300 will give your company unlimited attendance and a corporate QUSER membership.

Presentation 10am - Noon CDT on April 28th and 10am - Noon on April 29th.
• Group By, Having and Multi-Dimensional Grouping
A SQL SELECT can be much more than a simple SELECT * FROM query. Among others it includes with the GROUP BY clause a powerful data aggregation capability. A bunch of aggregate functions allows to count or summarize rows, to calculate the average over multiple rows, to determine the minimum and maximum row value. The GROUP BY clause may be widely known and used, but the HAVING clause which goes hand in hand with the GROUP BY is often a mystery. There may be other secrets waiting for being revealed … or did you know you can create all kinds of sub-totals and grand-total with an SQL GROUP BY?
• OLAP Specifications - Much more than running numbers
The first OLAP Functions were already introduced with Release 6.1, for building running numbers and ranks, in a specific sequence and even with the ability to restart numbering at a predefined level. At release 7.3 a bunch of additional OLAP functions and enhancements for existing OLAP functions are introduced. With these enhancements it is possible to easily build running totals or averages with or without level breaks. Data cannot only be accumulated over all rows, but also within a specific range. It is easy to access and compare the first, previous, next or last occurrence of the data within a partition. This session will give you an overview over the available OLAP functions and how they can be exploited
• Creating and Consuming JSON Data with SQL (Double Session)
JSON (Java Script Online Notation) is a data exchange format like XML, however JSON is simpler, less verbose and can be faster parsed than XML. In this way JSON becomes more and more important especially when data is exchanged by RESTful web services. The use of XML declines in favor of JSON.
With Release 7.3 TR 3 a bunch of scalar and aggregate functions were integrated into SQL, for generating JSON objects, arrays and for combining JSON components into complete documents.
The JSON_TABLE tables function for consuming JSON data was already introduced with Release 7.3 TR 1. With the JSON_TABLE table function JSON documents can be parsed, converted into a relational representation and data picked specifically out. Additional scalar functions allow SQL to directly access JSON documents located within the IFS (Integrated File System) or in source physical file members.
Last but not least IBM provides SQL http-functions for getting access to web services. In composition with the JSON_TABLE function the JSON answers returned by web services can be consumed within a SELECT-Statement.

GoToMeeting Only

Eden Prairie, MN 55344

United States

RSVP for this meeting!

Birgitta Hauser worked on the IBM i and its predecessors since 1992. She worked and works as traditional RPG Programmer but also as Database and Software Engineer, focusing on IBM i application and database modernization. In July, 2019 she joined Fresche Solutions as a contractor.
She graduated with a business economics diploma, and started programming on the AS/400 in 1992. She also works in consulting and education as a trainer for RPG and SQL developers.
Since 2002 she has frequently spoken at the COMMON User Groups and other IBM i and Power Conferences in Germany, other European Countries, USA and Canada.
In addition, she is co-author of two IBM Redbooks and also the author of several articles and papers focusing on RPG and SQL for a German publisher and IBM DeveloperWorks.
In 2015 she received the John Earl Speaker Scholarship Award. In 2018 she received the Al Barsa Memorial Scholarship Award.
Birgitta is also in 2020 an IBM Champion and will be joining us Via GoToMeeting from Germany!

Main menu

SQL Workshop - Remote Workshop with Hands On Exercises

Upcoming Events

Useful Links

Contact Us

Main menu

You are here

SQL Workshop - Remote Workshop with Hands On Exercises

Upcoming Events

Useful Links

Contact Us