Get started
Bring yourself up to speed with our introductory content.
iSeries SQL commands and statements
Running AS/400 query to find records based on date
Learn about a quick way of running an AS/400 query to select records based on current sysdate. Continue Reading
Tracking data changes on IBM i with triggers
Triggers can be implemented on the AS/400 using a high-level language or SQL and can be used to enforce business rules or validating input data. Learn how to use DB2 triggers as a means of auditing data inserts, deletes or changes. Continue Reading
Convert a numeric physical file to a character in SQL without leading zeros
In SQL, when converting a numeric physical file to digits, pass the results of the DIGITS function through the STRIP function in order to avoid leading zeros. Continue Reading
-
Inserting data from a CTE into a file in SQL
To insert data from the CTE into a file in SQL, the common table expression definitions need to be declared within the nested SELECT statement when using a SQL insert statement. Continue Reading
Running source member SQL statements with RUNSQLSTM
The RUNSQLSTM command expects a semi-colon (;) at the end of each SQL statement. Continue Reading
Enabling IBM i to access Microsoft SQL server data
Microsoft SQL Server does not fully support the open group DRDA standard, and thus middleware is needed to enable the AS/400 to access Microsoft SQL server data.Continue Reading
Using SQL to search within a DB2 field on IBM i
By taking advantage of the Row_Number function introduced in IBM i's V5R4, you can process a range of values within a field.Continue Reading
Selecting a PF with multiple members from SQL
The simplest technique for selecting a PF with multiple members on AS/400 from SQL is to create an alias.Continue Reading
Referencing a renamed column on SELECT clause in SQL
The SQL Standard does not allow a renamed column on the SELECT clause to be referenced on a different part of the SQL statement such as the WHERE clause. To do it on a single statement, an SQL common table expression can be used.Continue Reading
Using SQL on System i to create multicolored comment lines
When using SQL to color source code on the AS/400, it is also possible to create multicolored comment lines. This System i tip provides the queries to do so.Continue Reading
-
Joining files using SQLRPGLE
In DB/400, for a join operation returning a single row, the SELECT INTO statement can be used in SQLRPGLE.Continue Reading
Excluding characters from AS/400 DB2 SELECT statements prevents mapping errors
In DB2 on AS/400, rows with characters that cannot be converted to a numeric value (A or #) need to be excluded from a SELECT statement in order to avoid a data mapping error when running the INT function on GLSBL.Continue Reading
Using SQL syntax to override DB2 automatic column name generator on iSeries
If you don't want to drop and recreate a table to change the internal names in your AS/400 DB2, a special SQL syntax can solve your problem.Continue Reading
Making a file with an automatic key in iSeries with DDS keyword
If you want to create an automatic key using a DDS keyword on AS/400, Kent Milligan explains that any native writes performed against an SQL table created with an identity table will result in DB2 generating identity values.Continue Reading
Using SQL on System i to color source code and inline comments
Without use of an advanced emulator, how do you color source code? SQL. This tip provides a complete table of valid hex values for display colors and the SQL queries to implement it on your system.Continue Reading