Action disabled: source

DRAFT

Omnidex SQL: JOIN

Description

The JOIN statement can be used in conjunction with the Omnidex Query Language QUALIFY command, to allow qualifications across tables. A JOIN statement follows one or more QUALIFY statements against the from_table, and precedes one or more QUALIFY statements against the to_table.

Syntax

JOIN [ FROM ] from_table 
    USING from_join_col TO to_table USING to_join_col 
    [ OWNED BY table_name]
    [ ON [CURSOR ] 
    [ WITH options]

Discussion

[ FROM ] from_table_spec

Required. The FROM keyword is supported for visual clarity, but is optional. The from_table_spec is the table specified in the previous QUALIFY statement.

USING from_join_column_spec

Required - The USING keyword is required. The from_join_column_spec is the link column in the from_table_spec that will be joined to the link column in the to_table_spec.

TO to_table_spec

Required. The TO keyword is required. The to_table_spec is the table that will be joined with the table in the previous QUALIFY statement.

[ USING to_join_column_spec ]

Required - The USING keyword is required. The to_join_column_spec parameter is the link column in the to_table that will be joined to the link column in the from_table.

[ OWNED BY table_name ]

Optional - Allows the specification of the table representing the owner of the to_table

[ WITH options ]

Optional - Specify options to be used for this command.

Examples

The following example qualifies all orders in the Orders table that were canceled. It then joins the qualified orders to the products table which are further refined with another qualify. Finally, the qualified product names are retrieved with a select statement using the odxid WITH option.

>qualify orders where status='cncl'
610 ORDERS (453 CUSTOMERS) records qualify

>join from orders using product_no to products using product_no
99 PRODUCTS records join

>qualify products where product_class='and eqp'
7 PRODUCTS records qualify

>select product_name from products with odxid

PRODUCT_NAME
----------------------------------------
PAPER CUTTER
DIGITAL POSTAL SCALE
ONE STEP CAMERA
OVERHEAD PROJECTOR
RICOH FAX MACHINE
FAX SURGE PROTECTOR
POSTAL SCALE

7 rows returned
 
Back to top
dev/sql/statements/join/home.txt ยท Last modified: 2016/06/28 22:38 (external edit)