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:

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.

ProdCat ProdID ProdDesc ProdName ProdQuant
100570265 129930113 Wild Berry Summerbrook Wild Berry Seltzer 84
100570265 129930513 Apple-Cran Summerbrook Apple-Cran Seltzer -9
100570265 129935223 Black Currant Olympia Black Currant Seltzer 125
100570267 129935423 Sparkling Lemonade Olympia Sparkling Lemonade 7
100570262 761837634 Cherry Lime Olympia Cherry Lime Soda 54
100570263 761831637 Cranberry Ice Tea Crystal Falls Cranberry Ice Tea 302
100570263 761831635 Diet Peach Ice Tea Crystal Falls Diet Peach Tea 79
100570263 761831636 Diet Rasp Ice Tea Crystal Falls Diet Rasp Ice Tea 110
100570263 761831638 Lemon Ice Tea Crystal Falls Lemon Ice Tea 218
100570100 165712111 Mineral Water Whisper Rain Mineral Water 22
100570262 165712115 Mango Passion soda Dave’s Mango Passion soda -22