Once you have chosen an ODBC database using the ODBC Select Data Source
dialog, you are presented a list of tables and columns within the current
table found in the database. To get data from the database, select the table
from the drop down list of tables, and then transfer the required columns to
the Selected column list. Clicking OK will query the database for data
matching you selection and place this in a spreadsheet or directly into the
server if you are using the Data Load menu item.
This simple selection of columns from a table creates a SQL SELECT statement.
The SQL statement can be expanded if you know how to write SQL statements and
chose the Edit SQL option below. For further help on SQL statements see
SQL Statements.
Table
This drop down list contains all tables in the ODBC database, sorted by type
and then name. The type information is displayed before the column name. The
main table types are TABLE - standard/basic SQL table containing entered data,
VIEW - virtual table constructed by an internal SQL query within the database
(this may be formed from data from multiple tables within the database),
SYNONYM or ALIAS - an alternative name for a table in the database and SYSTEM
TABLE - non user defined database information.
When a new table is selected, its columns will be displayed in the available
column list. The names of some tables in SQL statements may require an extra
section in two tier databases such as Oracle which can group tables together
into SCHEMA.
Available Columns
A list of the columns in the current table. The column names are prefixed with
a single letter giving their type: C - text, N - Numeric and
D - Date. Date values are read in as text as ODBC databases do not have a
standard method of storing date values. These text dates can be converted to
GenStat 8 byte date values using the Convert menu.
Single Columns can be transferred to the selected column list by double
clicking them. Otherwise the selected columns can be transferred by clicking
the button. Multiple columns can be selected using the
Control or Shift key in conjunction with single clicking the mouse.
Selected Columns
An editable list of columns selected for retrieval from the currently selected
table.
Select All
Add all available columns into the selected column list.
Clear
Clear all columns from the selected column list.
Edit resulting SQL statement
Selecting this option will result in the ODBC: View or Edit SQL Statement
window popping up before the database query is run. This allows
you to edit and add to the SQL query generated by selecting a set
of columns from a single table. For example if you wanted data from
the resulting INNER JOIN of two tables, you could add in the SQL
code to select columns from a second table joined to the first one
you had selected. You can also specify other SQL options such
as SORTED BY and WHERE etc.
Save Retrieval in file for re-use
If you select this option,
you will be prompted for the name of a GenStat Stored ODBC
retrieval (.GDB) file after you query has run correctly.
The current ODBC database specification
and SQL statement will be stored in this file, and may be
rerun at a later date by just specifying this file in the
File Open dialog. This file is a standard text file and can
be edited using any text editor.
Next
Opens the ODBC Data Query - Filter Data Using Where Statement dialog. A Where clause
in an SQL statement restricts (or filters) the rows which are
returned by SQL query. The Clause is in the form of a logical
statement which must be TRUE for the row to be included in the
result set.
Finish
Close the dialog and run the SQL query.
Cancel
Close the ODBC dialog without querying the database.
button. Multiple columns can be selected using the
Control or Shift key in conjunction with single clicking the mouse.