You can use aliases, the INTO TEMP clause, and display labels to speed your way through joins and multiple-table queries and to produce output for other uses.
You can assign aliases to the tables in the FROM clause of a SELECT statement to make multiple-table queries shorter and more readable. You can use an alias wherever the table name would be used, for instance, as a prefix to the column names in the other clauses.
SELECT s.stock_num, s.manu_code, s.description,
s.unit_price, c.catalog_num,
c.cat_advert, m.lead_time
FROM stock s, catalog c, manufact m
WHERE s.stock_num = c.stock_num
AND s.manu_code = c.manu_code
AND s.manu_code = m.manu_code
AND s.manu_code IN ('HRO', 'HSK')
AND s.stock_num BETWEEN 100 AND 301
ORDER BY catalog_num The associative nature of the SELECT statement allows you to use an alias before you define it. In Figure 120, the aliases s for the stock table, c for the catalog table, and m for the manufact table are specified in the FROM clause and used throughout the SELECT and WHERE clauses as column prefixes.
Compare the length of Figure 120 with Figure 121, which does not use aliases.
SELECT stock.stock_num, stock.manu_code, stock.description,
stock.unit_price, catalog.catalog_num,
catalog.cat_advert,
manufact.lead_time
FROM stock, catalog, manufact
WHERE stock.stock_num = catalog.stock_num
AND stock.manu_code = catalog.manu_code
AND stock.manu_code = manufact.manu_code
AND stock.manu_code IN ('HRO', 'HSK')
AND stock.stock_num BETWEEN 100 AND 301
ORDER BY catalog_num Figure 120 and Figure 121 are equivalent and retrieve the data that Figure 122 shows.
stock_num 110
manu_code HRO
description helmet
unit_price $260.00
catalog_num 10033
cat_advert Lightweight Plastic with Vents Assures Cool
Comfort Without Sacrificing Protection
lead_time 4
stock_num 110
manu_code HSK
description helmet
unit_price $308.00
catalog_num 10034
cat_advert Teardrop Design Used by Yellow Jerseys; You
Can Time the Difference
lead_time 5
·
·
·
You cannot use the ORDER BY clause for the TEXT column cat_descr or the BYTE column cat_picture.
You can use aliases to shorten your queries on tables that are not in the current database.
Figure 123 joins columns from two tables that reside in different databases and systems, neither of which is the current database or system.
SELECT order_num, lname, fname, phone
FROM masterdb@central:customer c, sales@western:orders o
WHERE c.customer_num = o.customer_num
AND order_num <= 1010 By assigning the aliases c and o to the long database@system:table names, masterdb@central:customer and sales@western:orders, respectively, you can use the aliases to shorten the expression in the WHERE clause and retrieve the data, as Figure 124 shows.
order_num lname fname phone
1001 Higgins Anthony 415-368-1100
1002 Pauli Ludwig 408-789-8075
1003 Higgins Anthony 415-368-1100
1004 Watson George 415-389-8789
1005 Parmelee Jean 415-534-8822
1006 Lawson Margaret 415-887-7235
1007 Sipes Arnold 415-245-4578
1008 Jaeger Roy 415-743-3611
1009 Keyes Frances 408-277-7245
1010 Grant Alfred 415-356-1123For more information on how to access tables that are not in the current database, see Accessing Other Database Servers and the IBM Informix: Guide to SQL Syntax.
You can also use synonyms as shorthand references to the long names of tables that are not in the current database as well as current tables and views. For details on how to create and use synonyms, see the IBM Informix: Database Design and Implementation Guide.
By adding an INTO TEMP clause to your SELECT statement, you can temporarily save the results of a multiple-table query in a separate table that you can query or manipulate without modifying the database. Temporary tables are dropped when you end your SQL session or when your program or report terminates.
Figure 125 creates a temporary table called stockman and stores the results of the query in it. Because all columns in a temporary table must have names, the alias adj_price is required.
SELECT DISTINCT stock_num, manu_name, description,
unit_price, unit_price * 1.05 adj_price
FROM stock, manufact
WHERE manufact.manu_code = stock.manu_code
INTO TEMP stockman;
SELECT * from stockman;
stock_num manu_name description unit_price adj_price
1 Hero baseball gloves $250.00 $262.5000
1 Husky baseball gloves $800.00 $840.0000
1 Smith baseball gloves $450.00 $472.5000
2 Hero baseball $126.00 $132.3000
3 Husky baseball bat $240.00 $252.0000
4 Hero football $480.00 $504.0000
4 Husky football $960.00 $1008.0000
·
·
·
306 Shimara tandem adapter $190.00 $199.5000
307 ProCycle infant jogger $250.00 $262.5000
308 ProCycle twin jogger $280.00 $294.0000
309 Hero ear drops $40.00 $42.0000
309 Shimara ear drops $40.00 $42.0000
310 Anza kick board $84.00 $88.2000
310 Shimara kick board $80.00 $84.0000
311 Shimara water gloves $48.00 $50.4000
312 Hero racer goggles $72.00 $75.6000
312 Shimara racer goggles $96.00 $100.8000
313 Anza swim cap $60.00 $63.0000
313 Shimara swim cap $72.00 $75.6000You can query this table and join it with other tables, which avoids a multiple sort and lets you move more quickly through the database. For more information on temporary tables, see the IBM Informix: Guide to SQL Syntax and the IBM Informix: Administrator's Guide.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]