SQL Commands

EXPORT

EXPORT [statement] TO filename [ON [CURSOR] cursor] [WITH options]

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.

top

OdxSQL 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 /users/test/cncl.ost 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

Options

top