iSeries (AS/400) SQL Tips, Techniques, and Articles
Return to list of categories.
- 20 SQL tips from Search400
- 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 Review of SQL Selection Predicates by Doug Eckersley
- 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 Packed Data in 'Flat' Files by Kevin Forsythe
- Alternate SQL Row-Selection Criteria
- Alternate SQL Row-Selection Criteria, Take 2
- Alternate SQL Row-Selection Criteria, Take 3
- 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
- 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
- Calling SQL from REXX by Joe Pluta
- Calling SQL Functions Directly From a High Level Language Program
- Case-Insensitive Sorting and Record Selection in SQL/400
- Change a Substring with SQL
- Choosing SQLSTATE values for Procedures, Functions, and Triggers
- 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.
- Compiling an SQL Program by Barbara Morris
- Compute Number of days between two dates in SQL/400.
- Conditional Sorting with SQL
- Converting character fields to numeric fields with SQL
- Converting Nulls in SQL Left Outer Joins
- Converting Unconvertible Dates Using SQL by Ted Holt
- 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
- DB2 UDB for iSeries SQL CLI Frequently Asked Questions
- 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
- Don't Let SQL Name Your Baby, Take 2
- Embedded SQL
- Embedded SQL Jump Start by Sharon L. Hoffman
- Embedded SQL with Variables
- Embedding SQL in /free by Joel Cochran
- Empower SQL with Java User-Defined Functions
- Error Checking and Embedded SQL
- 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.
- Fast guide to SQL
- Fetch a Variable Number of Records with SQL
- Find Database Records with Invalid Dates
- Finding the Last Transaction for a Customer
- Flexible SQL Journaling by Kent Milligan
- Foolproof Guide to Embedded SQL Error Handling by Paul Conte
- Four Tips for Faster SQL Input by Paul Conte
- Full Outer Joins on DB2 for i5/OS by Gene Cobb
- 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
- 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
- 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
- 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 update iSeries databases using JDBC, SQL by Jim Mason
- 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
- Installing MySQL Today by Alfredo Delgado
- Joining on Mismatched Values
- Managing DB2 UDB for iSeries Schemas and Journals
- Maximize SQL Query Engine (SQE) Usage of Your DB2 Web Query Reports by Gene Cobb
- Mimic Green-Screen Interactive SQL Without Development Kit
- Missing In Action: The Full Outer Join
- More about SQL and Logical Files by Ted Holt
- More About SQL Correlation Names
- Multiformat SQL Data Sets
- Mystifying OS/400 and i5/OS Mathematics by Howard Arner
- No Automatic Casting for Char by Howard Arner
- 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
- Perform SQL Updates Using Multiple Files by Mike Faust
- Pivot Data with SQL/400 by Michael Sansoterra
- PREPARE to DECLARE by Mike Faust (Use DECLARE and PREPARE to improve SQL performance)
- Problem Fetching Duplicate Records with SQL/400
- Procedures, and Functions, and Catalogs! Oh My! by Scott Forstie
- Processing SQL Statements by Kent Milligan
- 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
- Remote SQL by Daniel Narkorn
- Repeated Characters in SQL
- Retrieve Multiple Result Sets from a Stored Procedure by Michael Sansoterra
- Retrieve SQL (RTVSQL) source statement
- Rounding Over a Set of Rows by Michael Sansoterra
- Row locks in DB2/400 UDB by Alexei Pytel
- 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
- Saving and Restoring External SQL Routine Definitions
- Scribble on SQL's Scratchpad by Michael Sansoterra
- Searching for Wildcard Characters by Ted Holt
- Selecting allowed SQL statements for profiles in Query Manager
- Selecting Data in Descending Key Sequence with SQL
- 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 subselects
- Speed Up Your UDF Performance by Kent Milligan
- SQL and i5/OS Record Format Names by Kent Milligan
- 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
- * New * SQL May Be Catching Up with DDS by Ted Holt
- SQL Message Finder
- SQL Record Selection with Dynamic Lists
- 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 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 Procedures and *LIBL By Sarah Poger, Sungard Futures
- Subfile page up & down using embedded SQL
- Syntax check for SQL statements. Example of using the Syntax Check SQL Statement (QSQCHKS) API.
- 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 SPACE Function Takes Up a Lot of Space
- Thinking in Sets
- Tips and tricks for fast SQL procedures
- Unusual But Logical SQL Sorting
- Update a Multi-Table View using INSTEAD OF Triggers
- 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
- 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 delete duplicate records
- Use SQL to Easily Update Multi-Key Files
- 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 DRDA to Execute SQL Statements on Another iSeries by Michael Sansoterra
- Using Parameter Markers with SQL Functions by Michael Sansoterra
- Using SQL on System i to color source code and inline comments
- Using SQL to do a Numeric search on Phone number.
- Using SQL to Find Duplicate Records by Ted Holt
- Using SQL's Common Table Expression Functionality by Kent Milligan
- Using the Create Table 'Field Reference' Feature by Paul Conte
- Using the SQL Features in Operations Navigator by Joel Cochran
- Using the SQL SET OPTION Statement by Michael Sansoterra
- 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 SQL Query Engine Delivers on Its Promise by Jarek Miszczyk
- Various OPNQRYF Examples
- Web Sites with SQL CLI Information by Scott Klement
- What Can I Select When I Group?
- * New * What Happened to My Key?
- WHERE Versus HAVING
- 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 Derived Columns by Skip Marchesani
- Write results of SQL Select statement to an outfile
