TWO Full Days SQL / RPG Seminar with Birgitta Hauser
Day 1: Wednesday - October 22, 2014

SQL – Queries – Do you know what you can do with a simple SELECT-Statement

Abstract: Today SQL Queries are not only used for ad-hoc Queries, but also replace more and more native I/O. The SQL Select statement is much more powerful than we can imagine. Currently SQL provides around 180 scalar functions and 20 aggregate functions. Besides various ways of joining tables together, data from multiple tables can be merged. Since Release 6.1 all kinds of Sub- and Grand Totals can be built. With recursive Common Table expressions or the hierarchical query clause hierarchical and bi-directional dependencies between the data can be analyzed. With release 7.1 new functions for building XML documents and for accessing XML data located in either a column of a table or in the IFS were introduced. And it’s even consuming Web-Services is possible with SQL. … In this workshop you will learn multiple ways for solving your problems by using a SQL Select statement.
Learning Objectives: Expand your SQL know-how with not-widely known scalar functions for string handling or numeric rounding. Handle different conditions with case-clauses within a single SELECT- Statement. Use multi-dimensional grouping for building totals. Join and merge tables and views. Instead of coding several SQL statements or building temporary tables, use subselects and/or common table expressions (CTE) including recursive CTE. Learn how recursive data can be analyzed. Create XML documents and pick data out of XML documents and convert them into a relational form. And finally consume Web-Services directly with SQL.
Audience: Advanced SQL users and programmers who want to enhance their SQL query know-how.
Agenda:
  • SELECT Statement
  • Like Predicate
  • Distinct
    • Distinct Predicate
    • Distinct and Aggregate Functions
    • Distinct and NULL Values
  • Scalar Functions
    • String Manipulation
    • Calculation with numeric Values
    • Date and Time Calculation
    • OLAP Ranking Functions
  • Case Clause
  • Multi-dimensional Grouping
    • Sub-Totals and Grouping Sets
  • Joining Tables and Views
    • Inner, Left/Right Outer, Full, Left/Right Exception and Cross Join
  • Common Table Expressions
    • Recursive Common Table Expressions
    • Hierarchical Query Clause
  • Consuming XML Documents
  • Consuming RESTfull Web-Services
Level:Advanced
Duration:Full-day
(Event Main Page)