Selecting Specific Columns
Often all you want to see is the data in one or more specific columns.
Again, the formula is to use the SELECT and FROM clauses, specify the
columns and table, and perhaps order the data in ascending or descending
order with an ORDER BY clause.
Selecting Specific Columns
If you want to find all the customer numbers in the orders table, use a statement
such as the one in Query 2-12.
Query 2-12
SELECT customer_num FROM orders
Query Result 2-12 shows how the statement simply selects all data in the
customer_num column in the orders table and lists the customer numbers on
all the orders, including duplicates.
The output includes several duplicates because some customers have placed
more than one order. Sometimes you want to see duplicate rows in a projection.
At other times, you want to see only the distinct values, not how often
each value appears.
Query Result 2-12
customer_num
104
101
104
106
106
112
117
110
111
115
104
117
104
106
110
119
120
121
122
123
124
126
127
l
Selecting Specific Columns
To suppress duplicate rows, include the keyword DISTINCT or its synonym
UNIQUE at the start of the select list, as Query 2-13 shows.
Query 2-13
SELECT DISTINCT customer_num FROM orders
SELECT UNIQUE customer_num FROM orders
To produce a more readable list, Query 2-13 limits the display to show each
customer number in the orders table only once, as Query Result 2-13 shows.
customer_num Query Result 2-13
101
104
106
110
111
112
115
116
117
119
120
121
122
123
124
126
127
Selecting Specific Columns
Suppose you are handling a customer call, and you want to locate purchase
order number DM354331. To list all the purchase order numbers in the orders
table, use a statement such as the one that Query 2-14 shows.
Query 2-14
SELECT po_num FROM orders
Query Result 2-14 shows how the statement retrieves data in the po_num
column in the orders table.
Query Result 2-14
po_num
B77836
9270
B77890
8006
2865
Q13557
278693
LZ230
4745
429Q
B77897
278701
B77930
8052
MA003
PC6782
DM354331
S22942
Z55709
W2286
C3288
W9925
KF2961
Selecting Specific Columns
However, the list is not in a very useful order. You can add an ORDER BY
clause to sort the column data in ascending order and make it easier to find
that particular po_num, as Query Result 2-15 shows.
Query 2-15
SELECT po_num FROM orders
ORDER BY po_num
po_num Query Result 2-15
278693
278701
2865
429Q
4745
8006
8052
9270
B77836
B77890
B77897
B77930
C3288
DM354331
KF2961
LZ230
MA003
PC6782
Q13557
S22942
W2286
W9925
Z55709
Selecting Specific Columns
To select multiple columns from a table, list them in the select list in the
SELECT clause. Query 2-16 shows that the order in which the columns are
selected is the order in which they are produced, from left to right.
Query 2-16
SELECT paid_date, ship_date, order_date,
customer_num, order_num, po_num
FROM orders
ORDER BY paid_date, order_date, customer_num
you can use the ORDER BY clause to sort the data in ascending or descending order and
perform nested sorts. Query Result 2-16 shows ascending order.
Query Result 2-16 paid_date ship_date order_date customer_num order_num po_num
05/30/1994 05/22/1994 106 1004 8006
05/30/1994 112 1006 Q13557
06/05/1994 05/31/1994 117 1007 278693
06/29/1994 06/18/1994 117 1012 278701
07/12/1994 06/29/1994 119 1016 PC6782
07/13/1994 07/09/1994 120 1017 DM354331
06/03/1994 05/26/1994 05/21/1994 101 1002 9270
06/14/1994 05/23/1994 05/22/1994 104 1003 B77890
06/21/1994 06/09/1994 05/24/1994 116 1005 2865
07/10/1994 07/03/1994 06/25/1994 106 1014 8052
07/21/1994 07/06/1994 06/07/1994 110 1008 LZ230
07/22/1994 06/01/1994 05/20/1994 104 1001 B77836
07/31/1994 07/10/1994 06/22/1994 104 1013 B77930
08/06/1994 07/13/1994 07/10/1994 121 1018 S22942
08/06/1994 07/16/1994 07/11/1994 122 1019 Z55709
08/21/1994 06/21/1994 06/14/1994 111 1009 4745
08/22/1994 06/29/1994 06/17/1994 115 1010 429Q
08/22/1994 07/25/1994 07/23/1994 124 1021 C3288
08/22/1994 07/30/1994 07/24/1994 127 1023 KF2961
08/29/1994 07/03/1994 06/18/1994 104 1011 B77897
08/31/1994 07/16/1994 06/27/1994 110 1015 MA003
09/02/1994 07/30/1994 07/24/1994 126 1022 W9925
09/20/1994 07/16/1994 07/11/1994 123 1020 W2286
Selecting Specific Columns
When you use SELECT and ORDER BY on several columns in a table, you
might find it helpful to use integers to refer to the position of the columns in
the ORDER BY clause.The statements in Query 2-17 retrieve and display the
same data, as Query Result 2-17 shows.
Query 2-17
SELECT customer_num, order_num, po_num, order_date
FROM orders
ORDER BY 4, 1
SELECT customer_num, order_num, po_num, order_date
FROM orders
ORDER BY order_date, customer_num
Query Result 2-17
customer_num order_num po_num order_date
104 1001 B77836 05/20/1994
101 1002 9270 05/21/1994
104 1003 B77890 05/22/1994
106 1004 8006 05/22/1994
116 1005 2865 05/24/1994
112 1006 Q13557 05/30/1994
117 1007 278693 05/31/1994
110 1008 LZ230 06/07/1994
111 1009 4745 06/14/1994
115 1010 429Q 06/17/1994
104 1011 B77897 06/18/1994
117 1012 278701 06/18/1994
104 1013 B77930 06/22/1994
106 1014 8052 06/25/1994
110 1015 MA003 06/27/1994
119 1016 PC6782 06/29/1994
120 1017 DM354331 07/09/1994
121 1018 S22942 07/10/1994
122 1019 Z55709 07/11/1994
123 1020 W2286 07/11/1994
124 1021 C3288 07/23/1994
126 1022 W9925 07/24/1994
127 1023 KF2961 07/24/1994
Selecting Specific Columns
You can include the DESC keyword in the ORDER BY clause when you assign
integers to column names, as Query 2-18 shows.
Query 2-18
SELECT customer_num, order_num, po_num, order_date
FROM orders
ORDER BY 4 DESC, 1
In this case, data is first sorted in descending order by order_date and in
ascending order by customer_num.
ORDER BY and Non-English Data
By default, Informix database servers use the U.S. English language environment,
called a locale, for database data. The U.S. English locale specifies data
sorted in code-set order. This default locale uses the ISO 8859-1 code set.
If your database contains non-English data, the ORDER BY clause should
return data in the order appropriate to that language. Query 2-19 uses a
SELECT statement with an ORDER BY clause to search the table, abonnés, and
to order the selected information by the data in the nom column.
Query 2-19
SELECT numéro,nom,prénom
FROM abonnés
ORDER BY nom;
The collation order for the results of this query can vary, depending on the
following system variations:
n Whether the nom column is CHAR or NCHAR data type. The
database server sorts data in CHAR columns by the order the
characters appear in the code set. The database server sorts data in
NCHAR columns by the order the characters are listed in the collation
portion of the locale. Store non-English data in NCHAR (or
NVARCHAR) columns to obtain results sorted by the language.
n Whether the database server is using the correct non-English locale
when accessing the database. To use a non-English locale, you must
set the CLIENT_LOCALE and DB_LOCALE environment variables to
the appropriate locale name.
GLS
Selecting Specific Columns
For Query 2-19 to return expected results, the nom column should be NCHAR
data type in a database that uses a French locale. Other operations, such as
less than, greater than, or equal to, are also affected by the user-specified
locale. Refer to the Guide to GLS Functionality for more information on non-
English data and locales.
Query Result 2-19a and Query Result 2-19b show two sample sets of output.
Query Result 2-19a follows the ISO 8859-1 code-set order, which ranks uppercase
letters before lowercase letters and moves the names that start with an
accented character (Ålesund, Étaix, Ötker, and Øverst) to the end of the list.
numéro nom prénom
Query Result 2-19a
13612 Azevedo Edouardo Freire
13606 Dupré Michèle Françoise
13607 Hammer Gerhard
13602 Hämmerle Greta
13604 LaForêt Jean-Noël
13610 LeMaître Héloïse
13613 Llanero Gloria Dolores
13603 Montaña José Antonio
13611 Oatfield Emily
13609 Tiramisù Paolo Alfredo
13600 da Sousa João Lourenço Antunes
13615 di Girolamo Giuseppe
13601 Ålesund Sverre
13608 Étaix Émile
13605 Ötker Hans-Jürgen
13614 Øverst Per-Anders
Query Result 2-19b
numéro nom prénom
13601 Ålesund Sverre
13612 Azevedo Edouardo Freire
13600 da Sousa João Lourenço Antunes
13615 di Girolamo Giuseppe
13606 Dupré Michèle Françoise
13608 Étaix Émile
13607 Hammer Gerhard
13602 Hämmerle Greta
13604 LaForêt Jean-Noël
13610 LeMaître Héloïse
13613 Llanero Gloria Dolores
13603 Montaña José Antonio
13611 Oatfield Emily
13605 Ötker Hans-Jürgen
13614 Øverst Per-Anders
13609 Tiramisù Paolo Alfredo
Selecting Specific Columns
Query Result 2-19b shows that when the appropriate locale file is referenced
by the data server, names starting with non-English characters (Ålesund,
Étaix, Ötker, and Øverst) are collated differently than they are in the ISO
8859-1 code set. They are sorted correctly for the locale. It does not distinguish
between uppercase and lowercase letters. ¨
Selecting Substrings
To select part of the value of a CHARACTER column, include a substring in the
select list. Suppose your marketing department is planning a mailing to your
customers and wants a rough idea of their geographical distribution based
on zip codes. You could write a query similar to the one Query 2-20 shows.
Query 2-20
SELECT zipcode[1,3], customer_num
FROM customer
ORDER BY zipcode
Using the WHERE Clause
Query 2-20 uses a substring to select the first three characters of the zipcode
column (which identify the state) and the full customer_num, and lists them
in ascending order by zip code, as Query Result 2-20 shows.
Using the WHERE Clause
Add a WHERE clause to a SELECT statement if you want to see only those
orders that a particular customer placed or the calls that a particular customer
service representative entered.
You can use the WHERE clause to set up a comparison condition or a join
condition. This section demonstrates only the first use. Join conditions are
described in a later section and in the next chapter.
The set of rows returned by a SELECT statement is its active set. A singleton
SELECT statement returns a single row. Use a cursor to retrieve multiple rows
in INFORMIX-4GL or an SQL API. In NewEra, you use a SuperTable to handle
multiple-row retrieval. See Chapter 5, “Programming with SQL,” and
Chapter 6, “Modifying Data Through SQL Programs.”
Query Result 2-20
zipcode customer_num
021 125
080 119
085 122
198 121
322 123
604 127
740 124
802 126
850 128
850 120
940 105
940 112
940 113
940 115
940 104
940 116
940 110
940 114
940 106
940 108
940 117
940 111
940 101
940 109
941 102
943 103
943 107
946 118
————————————————————————————————————-
kpvarkeys 