Back to Tylogix Home Page
Jump to the Tylogix SQL Useful Links Page

AS/400 SQL Question & Answer Page

By Thibault Dambrine
 

In this page, you will find a quick reference for the different tips and techniques I have encountered regarding SQL techniques on iSeries.

Questions about:

How to find duplicate records in a physical file using SQL?
How to manipulate data on a remote system?
How to execute a single SQL statement from a CL Program?

On how to find duplicate records on iSeries using SQL, James from Kentucky asked : I am trying to identify duplicate key fields within a file. What is the best way to do this? Answer: Here is an easy to follow technique. Assuming a table TABLE_X with column_1, column_2, and column_3, and assuming the aim is to find all columns who happen to have exact duplicate combination of data for these three colomns: You would key, in the SQL command line the following command: SELECT COLUMN_1, COLUMN_2, COLUMN_3, COUNT(*) FROM TABLE_X HAVING COUNT(*) > 1 GROUP BY COLUMN_1, COLUMN_2, COLUMN_3 Another example that can be used, using the Relative Record Number (equivalent of Row ID in Oracle), is SELECT * FROM TABLE_X T1 WHERE RRN(T1) < (SELECT MAX(RRN(T2) ) FROM TABLE_X T2                                WHERE ( T1.COLUMN _1 = T2.COLUMN_1 AND                                          T1.COLUMN _2 = T2.COLUMN_2 AND                                          T1.COLUMN _3 = T2.COLUMN_3 ) )   Go Back to the Top

On how to manipulate data on a remote system, Dan from California wrote: I work with for a company who owns several systems, amongst which there are AS/400. I can FTP from/to these systems but no DDM is allowed. Would there be a way to manipulate data on a remote system using FTP? Answer: To reach a remote site with SQL, you need to first connect and ensure the remote site is in the system directory. This is explained in detail in the Tylogix Remote SQL Technique page The best answer is perhaps not using FTP, even if a program can be remotely triggered on a remote system using the FTP instruction "QUOTE" - (more on this in the FTP Q&A Page). Go Back to the Top

On how to execute a single SQL statement from a CL program, Kris from Saskatchewan wrote: I am looking for a way to execute an SQL statement from a CL program without using home-made commands, any ideas? Answer: The RUNSQLSTM command is a CL command that reads and processes SQL statements stored in a source member. The statements in the source member can be run without compiling. This allows static SQL statements or dynamically generated SQL statements to be run without the need for embedding them in a high-level language such as RPG. Go Back to the Top

Thibault Dambrine is a Computer Consultant, based in Calgary, Alberta (Canada). He can be reached at (403) 263-6556 or via e-mail at dambrine@tylogix.com.