SQL Queries for Professionals

RPGIV and SQL Developers

It would be desirable if the participants were at least (reasonably) familiar with ACS Run SQL Script.

Select-Statement

• Basics
  • Generate New Columns
  • Where Conditions, Predicates
  • Order By

Scalar Functions

• String Functions (incl. Regular Expressions)
• Rounding Functions
• Converting between Data Types
• Date Calculation
• OLAP Specifications

CASE Clauses

NULL Values

Joining Tables and Views

• Inner Join
• Left/Right Outer Join
• Exception Join
• Cross Join
• Full Join

Union / Except / Intersect

Aggregate Functions

Group By and Having

• Multi-dimensional Grouping
• ListAgg and Split

(Nested) Sub-Selects

Common Table Expressions(CTE)

• Recursive Common Table Expressions (RCTE)
• Hierarchical Queries

Journaling Basics

• Generate Journal/-Receiver
• Register Tables/physical Files
• Commitment Control

Database Manipulation Commands

• Insert
• Update
• Delete