Title: SQL Basics - Retrieving information
Issue: Spectrum May/Jun '98
author:Nathan Rector
company:Natec Systems
email:nater@northcoast.com
http:www.northcoast.com/~nater/
I've covered how to setup your SQL/ODBC tables, but how do we retrieve the information? SQL/ODBC has a statement called 'SELECT' that is used to retrieve and display information for you.
The SELECT statement is similar to the Multi-value 'LIST' TCL command. If you are working with a simple SQL/ODBC table, then SELECT is easy to use. Let's use a simple customer file as an example. This customer file has the fields of ID, NAME, ADDRESS, CITY, STATE, ZIP, and PHONE.
If you want to display all these fields from TCL in a Multi-value database, all you need to do is create the following 'LIST' statement:
LIST CUST ID NAME ADDRESS CITY STATE ZIP PHONE
If you want to display the same fields with the SQL/ODBC SELECT statement you need to create the following statement:
SELECT ID, NAME, ADDRESS, CITY, STATE, ZIP, PHONE FROM CUST
As you can see, the SQL/ODBC statement is very similar to the Multi-value 'LIST' statement when working with this simple file.
In every database, there is a need to select only one record or all the records that meet a specific criteria. Again let's use the customer file and select all the items with NAME = "Joe]". The 'LIST' statement looks like the following:
LIST CUST WITH NAME = "Joe]" ID NAME ADDRESS CITY STATE ZIP PHONE
The SQL/ODBC statement looks like the following:
SELECT ID, NAME, ADDRESS, CITY, STATE, ZIP, PHONE FROM CUST WHERE NAME LIKE "Joe%"
If you want to only display record '101' in the customer file, the Multi-value 'LIST' statement looks like the following:
LIST CUST "101" ID NAME ADDRESS CITY STATE ZIP PHONE
The SQL/ODBC statement looks like the following:
SELECT ID, NAME, ADDRESS, CITY, STATE, ZIP, PHONE FROM CUST WHERE ID = "101"
Selecting more than one table:
Working with simple data files that are not multi-value in any way, the SELECT statement is easy to create. It similar to the 'LIST' statement, but when you start working with multi-value data, things become complex.
As I described in the Feb/Mar '98 issue of Spectrum, in SQL/ODBC, to create the multi-value effect that we are used to, you have to create more than one table. You are able to make a SELECT statement that selects and pulls information from multiple tables, but the complexity of the SELECT statement increases dramatically.
LIST ORDER ID PART_NO DATE_COMP DATE_DEL
Rec# Part# Comp Date Del Date
1001 1011-2 10/10/97 10/11/97
2087-1 10/10/97 10/11/97
To create the same result as this LIST statement did in SQL/ODBC, the select statement looks like the following:
SELECT ORDER.ID, ORDER_PART_NO.PART_NO, ORDER_PART_NO.DATE_COMP
ORDER_PART_NO.DATE_DEL FROM ORDER, ORDER_PART_NO WHERE
ORDER_PART_NO.ID = ORDER.ID AND ORDER.ID = "1001"
As the complexity of the file increases, the complexity of the SELECT statement increases as well.
Creating temporary fields:
Something else that we take for granted in our Multi-value databases is correlatives. We can create fields that use other fields to create a value and then use this field to select information. Once the field is defined, anyone can use it to create their reports or retrieve information. This is not so in SQL/ODBC. If you want to use correlatives, you have to define them directly into the SELECT statement. For example:
SELECT QTY, DOLLAR, QTY * DOLLAR FROM ORDER
This statement displays the QTY and dollar amount along with an additional field that is QTY * DOLLAR.
You are able to pull information from other SQL/ODBC tables just like you can in your Multi-value databases, but again, you have to define into your SELECT statement the information you wish to display. Let's look at an example of pulling customer information into a SELECT statement from the order file:
SELECT CUST.NAME, CUST.ADDRESS, ORDER.QTY, ORDER.DOLLAR FROM ORDER,
CUST WHERE ORDER.CUST_NO = CUST.ID
In order to display information from another file, you have to tell the SELECT statement which tables the information is coming out of. The WHERE ORDER.CUST_NO = CUST.ID tells the select statement that the customer information that is to be displayed is the customer ID found in the ORDER table in field CUST_NO.
The SQL/ODBC SELECT statement is very similar to the 'LIST' statement found in Multi-value databases. There is a lot of flexibility, but also can become very complicated depending on how much and the type of information you wish to display.