Using SQL: Examples

When transferring data from the AS/400 host to the PC, you can use the system default to transfer complete files, or you can use SQL (Structured Query Language) to send specific AS/400 data. SQL is a standardized query language for requesting information from a host database. You can use SQL to transfer data from specified fields and records in one or more host files, sort the transferred data, and perform simple calculations on the transferred data.

This section contains three short examples of how to use SQL to query data. The examples demonstrate various query keywords using sample data from an imaginary grocery store. There are three example queries:

• Example 1 is a simple query using SELECT, WHERE, and ORDER BY.
• Example 2 selects data from three files using JOIN BY.
• Example 3 transfers summary records using GROUP BY and HAVING.

To build a query, you must first specify the AS/400 data file(s) in the Lib/File(Member) box on the main AS/400 File Transfer screen. These examples use three sample AS/400 files named PRODUCT, SUPPLIER, and SUPPROD in a library named GROCERY. The first few records of each file are listed in the following tables.