EXPORT
The EXPORT statement is used to write the results of a query or the qualified
ID list from a previous call to the Omnidex subsystem, to an external
file. The external file is written to the server.
The EXPORT statement can be implemented in a couple of different ways.
These two methods differ from each other in their implementation significantly,
but ultimately can produce the same results.
The easiest method to export data is to replace the SELECT keyword with
EXPORT, in any SQL select statement. The query is processed like any other
select statement and the results are exported to the specified external
file. The following example exports all rows and columns from the customers
table where the state is "CO". The rows are exported to "myfile"
without any options.
export * from customers where state='co' to myfile
Another method used to export data is to execute one or more QUALIFY
commands, and then execute an "EXPORT" command with odxid or
odxsi WITH options. This example qualifies all customers in Colorado and
California. Then further qualifies the customers to those only in the
cities of Boulder, Denver, and Los Angeles. Finally, the ODXIDs are exported
to "myfile" using the ODXID with option.
qualify customers where state in ('co','ca')
qualify customers where city in ('and boulder','denver','los angeles')
export to myfile with 'ODXID'
When using the ODXID option, only the internal ids are exported, not
the data. These ids can, however, be used as criteria for other qualifications.
The following example finds all customers in the state of Colorado except
Dynamic Information Systems by first exporting the ODXID for all companies
named Dynamic Information Systems, to file1. Next, all companies in the
state of Colorado are compared to the list of ODXIDs in file1, leaving
only those not named Dynamic Information Systems. Finally, the primary
keys are exported to another external file, "results", using
the ODXSI option.
qualify customers where company='dynamic information systems'
export to file1 with odxid, delete
qualify customers where state='co'
qualify customers where "$odxid" = 'and not $file1'
export to results with odxsi, delete
Syntax
EXPORT [statement] TO filename [ON [CURSOR] cursor] [WITH
options]
EXPORT
Required
statement
Optional - The statement parameter defines what data is to be exported,
in SQL select statement format. If omitted, the export must be preceded
by a "QUALIFY" command.
TO filename
Required -The file spec of the file to be exported to. Use the APPEND
option to export data to an existing file without overwriting it, or the
DELETE option to overwrite the existing file. It is a good idea to fully
qualify the output file to ensure exactly where the file will be written.
[ON [CURSOR] cursor]
Optional - Specify which cursor to perform the export on. Default is the
current cursor. The "CURSOR" keyword is optional.
[WITH options]
Optional - Specify options to be used for this command.
Options
BUFFSIZE=n -- determines the size in
bytes ( n) of the buffer that is used to transfer rows. The default size
of n is 8192 bytes. The maximum size of n is 65536 bytes. The minimum
size of n is the combined length of the columns in the preceding oaselect
columns list.
CHAR=n -- converts binary
data to character format during export, where n is the byte
length of the exported number. The default is 32 bytes. Character
data is left-justified and padded with trailing spaces.
DATA -- transfers the data
in the most recently selected rows to the specified transfer
file. This is the default behavior of oaexport if no option
is specified.
DELETE -- deletes any existing
file with the specified name (passed through filename) before
creating a transfer file.
FILELIMIT=n -- determines
the maximum number of rows ( n) that the specified transfer
file can hold. The default size of n is 1000000. Note: The
FILELIMIT option only affects files on the MPE/iX operating
system.
ODXID -- transfers the internal
identifiers of the most recently qualified subset to the specified
transfer file. You can use these files in an oaqualify search,
as described in “Using oaexport’s ID files (keyword-only
searches)”.
ODXSI -- transfers the primary
key values of the most recently qualified rows to the specified
transfer file. You can use these files in an oaqualify search,
as described in “Using oaexport’s ID files (keyword-only
searches)”.
RECNO -- exports OMNIDEX
row numbers.
ROWID -- transfers the native
row identifiers of the most recently qualified rows to the
specified transfer file.
TEMPFILE -- (MPE/iX and OpenVMS
only) creates the specified transfer file as a temporary file
that is removed when you log out.
Example
This example creates an OST that contains customers who have canceled
their order. Then customers whose orders are on back order, are added
to the OST.
>export company, contact, state, status from customers,
orders where customers.customer_no=orders.customer_no and status='cncl'
to cncl.ost with ost
79 rows exported to cncl.ost
>attach ost cncl as CANCELED
>export company, contact, state, status from customers,
orders where customers.customer_no=orders.customer_no and status='back'
to cncl.ost with ost, append
621 rows exported to cncl.ost
>select company, contact from customers where state='CO'
...
22 rows returned
>detach ost canceled
table CANCELED detached
Top
|
