COMMON Fall Conference & Expo

Today SQL is not only used for ad-hoc queries, but replaces more and more native I/O and must be used in composition with all programming languages other than RPG and COBOL. Moving business logic into data base is one of the main goals when we talk about application modernization. The first step for realizing this goal is the ultimate use of the SQL SELECT statement which may be much more powerful than you can imagine. Currently SQL provides around 180 scalar functions and 20 aggregate functions. Besides various ways of joining tables or views, data from multiple tables can be merged. Since Release 6.1 all kinds of sub- and grand totals can be built. Recursive Common Table Expressions and hierarchical query clauses allow recursive data (such as bill of materials or flight plans) to be analyzed. XML functions for generating and consuming XML data located in either a table or within the IFS were introduced in release 7.1. Since release 7.2 TR 1 regular expressions can be used. Release 7.3 offers a new range of OLAP-Functions … In this workshop you will learn multiple ways for solving your requirements by using a SQL Select Statement. Learning Objectives: Expand your SQL know-how with not-widely known scalar functions for string handling including regular expressions 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 sub-selects and/or common table expressions (CTE) including recursive CTE. Learn how recursive data can be analyzed.