iSeries (AS/400) SQL Tips, Techniques, and Articles
Return to list of categories.
- 10 Easy Steps to a Complete Understanding of SQL
- 20 SQL tips from Search400
- A Brief Introduction To The SQL Procedures Language
- A Couple Cool Cross Joins - Learn how to use SQL cross joins to accomplish some difficult tasks. by Kevin Forsythe
- A Database Union is Not a Join
- A Handy SQL Timestamp Function
- A Review of SQL Selection Predicates by Doug Eckersley
- A Second Look At SQL Descriptors
- A Way to Capture SQL Statements by Kristian Bryant
- Access Packed Data in 'Flat Files' with a Table Function
- Access Your iSeries Data Quickly and Simply by Steve Close
- Accessing a Remote AS/400 Database via SQL by Paul Conte
- Accessing Database Members with SQL by Bob Cozzi
- Accessing External Data Using DB2 UDB DataLinks by Kevin Vandever
- Accessing Multiple DB2 Relational Databases In A Single Query
- Accessing Packed Data in 'Flat' Files by Kevin Forsythe
- Adding columns conditionally using SQL via a stored procedure
- Alternate SQL Row-Selection Criteria
- Alternate SQL Row-Selection Criteria, Take 2
- Alternate SQL Row-Selection Criteria, Take 3
- Alternatives To SQL Literals
- An SQL Pivot Procedure
- AS/400 and SQL Reference Pages
- Automatically Generate an SQE Plan Cache Snapshot by Gene Cobb
- Avoid "divide by zero" in query and SQL by Fred Wagaman
- Beware The Temporary Table
- Blend SQL and RPGLE to Make Better Tools by Kevin Forsythe
- Build a New Interface to Legacy Applications with SQL UDTFs by Jean-Paul Lamontre
- Build SQL to Define Your Existing Files
- Build SQL to Define Your Existing Files, Revisited
- Build SQL to Define Your Existing Files, Take 2
- Calculate a Fractional Number of Years Difference Between Two Dates in SQL
- Call iSeries Programs Using SQL (Stored Procedures)
- Calling SQL from REXX by Joe Pluta
- Calling SQL Functions Directly From a High Level Language Program
- CASE Simplifies SQL Update
- Case-Insensitive Sorting and Record Selection in SQL/400
- Case-Sensitive SQL Identifiers
- Change a Substring with SQL
- Choosing SQLSTATE values for Procedures, Functions, and Triggers
- Closing the Gaps
- Coalesce Function instead if IFNULL
- Coding SQL Functions in OS/400 V5R1 by Howard F. Arner Jr.
- Common Sense Comparisons - compare numeric data to character data, with the computer handling the conversion.
- Common Table Expressions Ease System Conversion
- Compiling an SQL Program by Barbara Morris
- Compute Number of days between two dates in SQL/400.
- Conditional Inserts with JDBC Prepared Statements
- Conditional Sorting with SQL
- Conditional SQL I/O by Ted Holt
- Control the Library List from a SQL Server Linked Server Definition
- Converting character fields to numeric fields with SQL
- Converting Nulls in SQL Left Outer Joins
- Converting Unconvertible Dates Using SQL by Ted Holt
- Counterintuitive Table Creation
- Create Database Files from SQL Queries
- Create your own RUNSQL cmd for use in CLP.
- Create Your Own SQL User-Defined Functions (UDFs)
- Creating SQL Functions with RPG IV Subprocedures by Kevin Forsythe
- Creating SQL Materialized Queries by Kevin Forsythe
- Creating Stored Procedures.
- Current-Date-to-Numeric-Date Comparisons by Mike Faust
- Data Scrubbing Functions In DB2 For i
- Database Harmony: "Traditional" and SQL Coexistence
- DATFMT Error in SQL
- DB2 Basics: Fun with Dates and Times
- DB2 for i - Services
- DB2 For i Scalar Function Performance Considerations
- DB2 UDB for iSeries SQL CLI Frequently Asked Questions
- Dealing With A MIN/MAX Quirk
- Debug CLI
- Debug SQL Objects More Easily by Kent Milligan
- Debugging Multi-Result Set DB2 Stored Procedures by Michael Sansoterra
- Deleting Records with Duplicate Data -- The Easy Way
- Deleting the Database Host Server's IBM SQL/400 Package - error message CPD4392
- Delimited List Processing in SQL by Michael Sansoterra
- Designing SQL Procedures and Functions
- Determine The State Of The Error
- Displaying Multiple Results Sets in Run SQL Scripts by Skip Marchesani
- Distinctly Speeding Up DISTINCT
- Don't Ignore the View by Paul Tuohy
- Don't Let SQL Name Your Baby, Take 2
- Easier Overloading of SQL Functions
- Embedded SQL
- Embedded SQL - Filtering data for a subfile as an example
- Embedded SQL - Retrieve a record count using a dynamic File name
- Embedded SQL Jump Start by Sharon L. Hoffman
- embedded SQL Technique (BIF) to perform a %LIKE% in RPG
- Embedded SQL with Variables
- Embedding SQL in /free by Joel Cochran
- Empower SQL with Java User-Defined Functions
- Enable Programmatic Access to Remote DB2 Data Using DRDA
- Enable Row Set Paging in a Client/Server Environment Using SQL
- Error Checking and Embedded SQL
- Establish SQL temporary storage limits
- Every Bucket Deserves a Descriptive Name
- Execute SQL Statements on DB2 UDB for Windows from the iSeries by Michael Sansoterra
- Execute SQL statements using QMQRY. Works on systems without the SQL product.
- Exploit the Power of Joins in SQL SELECT Statements by Kevin Forsythe
- Fast guide to SQL
- Fetch a Variable Number of Records with SQL
- Find A View Of A View Of A View. . .
- Find Database Records with Invalid Dates
- Finding SQL Server Mode Connecting Jobs by Scott Forstie
- Finding the Last Transaction for a Customer
- First Normal Form On The Fly
- Flexible SQL Journaling by Kent Milligan
- Foolproof Guide to Embedded SQL Error Handling by Paul Conte
- Formatting Dates with SQL
- Formatting Dates with SQL, Take 2
- Formatting Dates with SQL, Take 3
- Formatting Dates With SQL, Take 3a
- Fortifying Security With SQL by Elvis Budimlic
- Four Tips for Faster SQL Input by Paul Conte
- Full Outer Joins on DB2 for i5/OS by Gene Cobb
- Generate SELECT For All Columns
- Generating An Insert Script From Existing Data
- Generating DDL Source Using a CL Command by Mark Theuer , Mike Venz and Rafal Konik
- Get Creative Using the SQL Database Exit Point by Michael Sansoterra
- Getting the Message Using an SQL UDTF by Mike Faust
- Global Temporary Tables and Host Variables
- Good Reasons to Use Unrequired Correlation Names by Ted Holt
- Gotcha Lurking in Datalink File Manager for DB2/400
- Green-Screen SQL Alternatives
- Grouping a Union
- Guru: I'm A Number, You're A Number, Everybody's A Number
- Guru: SQL Can Generate A Series
- Guru: SQL Checks For Control Breaks
- Guru: SQL Facts Of UNION And ORDER BY
- Guru: SQL Facts Of UNION And ORDER BY, Take Two
- Guru: SQL Table Functions Can Do Non-function Things
- Guru: Three Ways To Manage Unmatched Data
- Guru: Using Identity Columns For Complex Keys
- Halt Those Long-Running SQL Queries
- Handle Multiple Members Within SQL by Kevin Forsythe
- Handling SQL Return Codes, Part I by Sam Lennon
- Handling SQL Return Codes, Part II by Sam Lennon
- Hierarchical Queries with DB2 Connect By A new method for recursively processing data relationships
- How Do I Find What's Not There?
- How Many Rows Did SQL Fetch?
- How to Count with SQL by Ted Holt
- How to have the results of a Select statement written to an outfile.
- How To Insert With A Common Table Expression
- How to update iSeries databases using JDBC, SQL by Jim Mason
- i Can ... Cancel SQL Statements
- I would like to create the host variable of the where in statement on the fly. Everything I've tried has not worked. Any suggestions?
- Improve Your Productivity with Significant Enhancements in DB2 SQL (Master nested compound statement handlers, implicit name qualification, and other advanced programming techniques.)
- Index Evaluator - New Tool for Analyzing DB2 Performance by Kent Milligan
- Inserting data from a CTE into a file in SQL
- Installing MySQL Today by Alfredo Delgado
- Joining on Mismatched Values
- Joining On Ranges
- Ken Likes FMTDATE
- Let One Row Represent a Group
- Let One Row Represent a Group,Take 2
- Make SQL Edit the Way You Want It To
- Making the Best Use of V6 SQL OLAP Functions by Mike Cain
- Managing DB2 UDB for iSeries Schemas and Journals
- Managing SQL Is Easy with Change Management Tools by Ray Bernardi
- Maximize SQL Query Engine (SQE) Usage of Your DB2 Web Query Reports by Gene Cobb
- MERGE ahead - Introducing the DB2 for i SQL MERGE statement
- Mimic Green-Screen Interactive SQL Without Development Kit
- Mimicking Magnetic Tape in SQL by Joe Celko
- Missing In Action: The Full Outer Join
- More about SQL and Logical Files by Ted Holt
- More About SQL Correlation Names
- More-Flexible SQL Triggers by Kent Milligan
- Multiformat SQL Data Sets
- MySQL and IBM i: Where Does MySQL Fit in a DB2 World? by Joe Pluta
- Mystifying OS/400 and i5/OS Mathematics by Howard Arner
- Native Regular Expressions In DB2 For i 7.1 And 7.2
- Next number routine via SQL
- No Automatic Casting for Char by Howard Arner
- Not only XML. SQL,JSON and XML made easy for IBM i
- Numbering Rows in an SQL Result Set
- Obtaining the name of the month in SQL
- Odd Ways to Round Numbers (ROUND Function)
- OLAP Ranking Specifications - New in V5R4
- Omit Commas from Numeric Dates [SQL]
- ON vs. ON
- ON vs. WHERE
- One Common Cure for SQL0901 Package Errors
- Optimize Your SQL Statements with Visual Explain
- Output queue entries information via SQL
- Paging Cursors And Position To
- Parsing Delimited Text Data Natively in SQL, Part 1
- Parsing Delimited Text Data Natively In SQL, Part 2: Java To The Rescue
- Passing an Entire Result Set as a Parameter: Part 1
- Passing an Entire Result Set as a Parameter: Part 2
- Perform SQL Updates Using Multiple Files by Mike Faust
- Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers
- Pivot Data with SQL/400 by Michael Sansoterra
- Practical SQL: Archiving Data with SQL by Joe Pluta
- Practical SQL: DB2 at Home, Part 1, the Environment
- Practical SQL: DB2 at Home, Part 2, First Look
- Practical SQL: DB2 at Home, Part 3, Using XML Data
- Practical SQL: Don't Be Afraid of Recursion by Joe Pluta
- Practical SQL: Dynamic Selection Criteria
- Practical SQL: GET DIAGNOSTICS
- Practical SQL: Monitor Changes by Accessing Journals with SQL by Joe Pluta
- Practical SQL: More Dynamic Selection Criteria
- PREPARE to DECLARE by Mike Faust (Use DECLARE and PREPARE to improve SQL performance)
- Presenting Vertical Data Horizontally
- Prevent Overlapping In Range Tables
- Problem Fetching Duplicate Records with SQL/400
- Procedures, and Functions, and Catalogs! Oh My! by Scott Forstie
- Processing SQL Statements by Kent Milligan
- Protection From Slow Queries
- Putting Run SQL Scripts to Work for You by Skip Marchesani
- Query Remote Database Tables from the iSeries Using SQL and Java by Michael Sansoterra
- Read a Data Area As a One-Row Table with SQL
- Recursive Queries on the iSeries and System i by Michael Sansoterra
- Redundant Join Criteria: Good or Bad Idea?
- Remote SQL by Daniel Narkorn
- Repeated Characters in SQL
- Replacing a DDS Physical File with an SQL Table
- Retrieve Multiple Result Sets from a Stored Procedure by Michael Sansoterra
- Retrieve SQL (RTVSQL) source statement
- Retrieving and Storing SQL Source for DB2 Database Objects by Skip Marchesani
- Retrieving Data From All Members With SQL
- Rounding Over a Set of Rows by Michael Sansoterra
- Row locks in DB2/400 UDB by Alexei Pytel
- Row Value Expressions Simplify Complex Row Selection
- RPG-SQL precompiler's version of the H spec
- RTVSQLSRC by Robert Cozzi, Jr.
- Run CL Commands From Interactive SQL
- Run SQL with output to an Excel file
- Running multiple SQL statements
- RUNSQLSTM Is Bigger and Better in V6R1
- Sample UDFs for Migration
- Saving and Restoring External SQL Routine Definitions
- Scribble on SQL's Scratchpad by Michael Sansoterra
- Searching for Wildcard Characters by Ted Holt
- SELECT from INSERT: New SQL Function in System i V6R1
- Selecting a PF with multiple members from SQL
- Selecting allowed SQL statements for profiles in Query Manager
- Selecting Data in Descending Key Sequence with SQL
- Show result of SQL expression evaluation on the message line
- Side-By-Side Lists
- Similar 'group by', but horizontal in SQL by Alex Corretge i Bergua
- Simple Error Handling in Embedded SQL by Doug Eckersley
- Simulated SETLL with SQL - For sorting a subfile.
- Simulating a Full Outer Join
- Soundex Function
- Speed Up SQL Procedures with Service Program Support by Kent Milligan
- Speed up SQL subselects
- Speed Up Your UDF Performance by Kent Milligan
- SQL 101: Date-Related Functions, Part 1—Converting Almost Anything to a Date, Time, or Timestamp
- SQL 101: Date-Related Functions, Part 2—Time-Saving Functions
- SQL 101: String-Related Functions Part 1 - Converting Almost Anything to a String
- SQL 101: String-Related Functions, Part 2 - Changing Case of a String Without %XLATE and More
- SQL 101: String-Related Functions, Part 3 - Pulling Strings Around with REPLACE and INSERT
- SQL 101: String-Related Functions, Part 4—Stringscaping with TRIM and Related Functions
- SQL 101: String-Related Functions, Part 5—Cutting and Stitching Strings
- SQL and Conversion Strategies
- SQL and i5/OS Record Format Names by Kent Milligan
- SQL Arrays in DB2 for i 7.1
- SQL Assist: Powerful Interactive SQL by Skip Marchesani
- SQL Call Level Interface (CLI) Resources
- SQL Can Return One or a Few Records
- SQL Cheatsheet
- SQL Communications Area by Kevin Forsythe
- SQL Cross Platform Interoperability: The Proper Function by Michael Sansoterra
- SQL Cursor Won't Close! by Scott Klement
- SQL Data Definition Language (DDL) - how to define alternate field names for use in RPG programs.
- SQL DayOfWeek Functions for Non-Date Data
- SQL defined database: Enjoy the view by Paul Tuohy
- SQL Doesn't Like Logical Files by Ted Holt
- SQL GetMiles Function - Use an SQL user-defined function to calculate the distance between ZIP codes.
- SQL Goodies in DB2 for i5/OS V5R4, Part 1 by Michael Sansoterra
- SQL Goodies in DB2 for i5/OS V5R4, Part 2 by Michael Sansoterra
- SQL indexes and native I/O – no contradiction
- SQL May Be Catching Up with DDS by Ted Holt
- SQL Message Finder
- SQL Paging With Limit And Offset In DB2 For i by Michael Sansoterra
- SQL Performance: IN vs. EXISTS
- SQL Quickly and Dirtily Extracts a Field from a CSV File by Ted Holt
- SQL Record Selection with Dynamic Lists
- SQL server error message -321
- SQL Stored Procedure Example by Scott Klement
- SQL Stored Procedure Example by Scott Klement ( a Follow-Up )
- SQL Subselect: Soup to Nuts by Skip Marchesani
- SQL Triggers and Other Trigger Enhancements in V5 by Skip Marchesani
- SQL Tutorial
- SQL Tutorial - SQL Reference and Programming Examples
- SQL Tutorials and reference
- SQL User Defined Functions in RPG by Scott Klement
- SQL vs. DDS -- The good, the bad and the downright ugly
- SQLCOD End-of-File Gotcha by Sam Lennon
- SQLSTATE Constants - Here is a work-in-progress list of constant names for the SQLSTATE codes in RPG.
- Squirrel: The universal SQL client by Jim Mason
- Squirrel: The universal SQL client by Jim Mason
- Staggering SQL String Handling with Regular Expressions by Michael Sansoterra
- Stop Using DDS! A Better Way to Make Files by Scott Klement
- Stored Procedure stopped working after applying a PTF?
- Stored Procedure versus SQL Function in an SAP Solution
- Stored Procedures and *LIBL By Sarah Poger, Sungard Futures
- Subfile page up & down using embedded SQL
- Subqueries vs. Joins
- Syntax check for SQL statements. Example of using the Syntax Check SQL Statement (QSQCHKS) API.
- SYSTOOLS.HTTPPOSTCLOBVerbose examples
- TechTip : Embedded SQL - Easy String Manipulation Using SQL Functions
- TechTip: Easier DB2 for i Data Transfers with New Technology Refresh
- TechTip: Exploit DB2 Web Query's Defined and Computed Fields by Tyler Even
- TechTip: IBM i Services - What It Is
- TechTip: Wresting Your Files Away from SQL Pseudo-Close
- Testing for Valid Data Representation in SQL
- The Case for SQL's SOUNDEX by Scott Klement
- The Dangers of Dynamic SQL by Ted Holt
- The Power and Magic of LPG by Mike Cain and Rob Bestgen
- The Powerful SQL Upsert
- The SPACE Function Takes Up a Lot of Space
- The State of the UNION
- The Three Sources Of RUNSQLSTM
- Thinking in Sets
- Three Powerful SQL Words
- Throw Away Old Summary Query Techniques by Ted Holt
- Tips and tricks for fast SQL procedures
- Two Ways to Prevent Division by Zero in SQL
- Two Ways To Rollup
- TYLOGIX SQL Tips & Techniques Page
- Unusual But Logical SQL Sorting
- Update a Multi-Table View using INSTEAD OF Triggers
- Update One File Based on Another File
- Update Only One of Duplicate Records
- UPDATE primary file with JOIN?
- Updating One Table from Another with SQL
- Updating through a Join with SQL
- Updating Through an SQL Cursor
- Updating Two or More Fields Through a Join
- Upsert One Row From A Data Structure
- Use Field Names for Column Headings
- Use Host Variables to Pass Data from RPG to SQL Statements by Paul Weyer
- Use Parameter Markers in SQL Persistent Stored Modules
- Use SQL Columnar Functions for Statistics, Summing, and Counting by Mike Faust
- Use SQL To Change A Substring
- Use SQL to delete duplicate records
- Use SQL to Easily Update Multi-Key Files
- Use SQL to Export and Delimit Files on the IBM i by Thomas Snyder
- Use SQL to list details and generate summary information
- Use SQL to Remove A selected Character From A Character Field
- Use SQL to Remove Extra Spaces
- Use SQL to Run PDM Options
- Use SQL to SELECT data directly to a CSV file
- Use SQL to Send Database Files via FTP
- Use SQL to Strip Out Tab Characters
- Use SQL User-Defined Functions to Avoid Data Repetition Problems by Michael Sansoterra
- Use the interactive SQL product to connect to any defined SQL data base including Access.
- Use UNION to generate totals
- Using CURRENT(DATE) to Select Records Using Query/400
- Using DRDA to Execute SQL Statements on Another iSeries by Michael Sansoterra
- Using Lateral Correlation To Define Expressions In DB2 For i
- Using Parameter Markers with SQL Functions by Michael Sansoterra
- Using SQL in the Real World by Joe Pluta
- Using SQL on System i to color source code and inline comments
- Using SQL on System i to create multicolored comment lines
- Using SQL to do a Numeric search on Phone number.
- Using SQL to Find Duplicate Records by Ted Holt
- Using SQL to Interrogate Journal Objects
- Using SQL's Common Table Expression Functionality by Kent Milligan
- Using the Create Table 'Field Reference' Feature by Paul Conte
- Using the QAQQINI File
- Using the SQL Features in Operations Navigator by Joel Cochran
- Using the SQL SET OPTION Statement by Michael Sansoterra
- Using UNION for Data Analysis by Skip Marchesani
- V5R3 SQL Enhancements
- V5R3 SQL Insert Improvement
- V5R3's New SQL BIFs, Part 1 by Mike Faust
- V5R3's New SQL BIFs, Part 2 by Mike Faust
- V5R3's New SQL BIFs, Part 3 by Mike Faust
- V5R4 SQL Enhancements by Mike Faust
- V6R1 Enhancements for Run SQL Scripts by Skip Marchesani
- V6R1 SQL Query Engine Delivers on Its Promise by Jarek Miszczyk
- Validate DBCS-Open Data
- Various OPNQRYF Examples
- Visual Explain for Run SQL Scripts by Skip Marchesani
- Web Sites with SQL CLI Information by Scott Klement
- What Can I Select When I Group?
- What Happened to My Key?
- WHERE Versus HAVING
- Whitepaper - Detailed SQL Analysis
- Whitepaper - The State of Database Management
- With Perseverance and Luck, You Can Conquer the DB2 Web Query Install by Chris Smith
- Work Fields and SQL
- Work with Data Returned from SQL SELECT Statements Using Cursors by Paul Weyer
- Working with Data Sets by Dan Cruikshank
- Working with Derived Columns by Skip Marchesani
- Write results of SQL Select statement to an outfile
