Example 1: Using SELECT, WHERE, and ORDER BY

To transfer a list of all available types of tea drinks sorted alphabetically:

  1. Specify the GROCERY/PRODUCT file in the Lib/File(Member) field on the main file transfer screen, then open the From AS/400 - Options dialog box:

    GUID-D6A7F569-8F8B-46E8-AFAC-A28995D2AE7E-low.jpg

  2. Verify that the Select field on the Overview page contains an asterisk (*). This indicates that all fields in the PRODUCT file will be transferred.
  3. To transfer all records containing the string Tea, type the following string in the Where field:

    PRODNAME LIKE ‘%Tea%’

    The % character is a wildcard indicating any number of characters:

    GUID-1C8DD1D9-94DC-4723-A8B9-43DD4F73B4FF-low.jpg

  4. To sort the data in ascending order by the type of drink, type the following string in the Order By field:

    PRODDESC

  5. Click Apply to update the query fields: 
    SELECT *
FROM grocery/product
WHERE (PRODNAME LIKE ‘%Tea%’)
ORDER BY PRODDESC

The resulting records are:

ProdID ProdDesc ProdName ProdQuant
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
Parent topic: Using SQL: Examples