In order to bring you the best possible user experience, this site uses Javascript. If you are seeing this message, it is likely that the Javascript option in your browser is disabled. For optimal viewing of this site, please ensure that Javascript is enabled for your browser.
Login  |   Cloud ERP  |   Home  |   qad.com




Using Inner Joins in Data Objects
An inner join is essentially a filter that consists of fields and/or tables that may or may not be part of the current data object. Using inner joins enhances your ability to extract the types of data you want from business objects in your system.
For example, you could create an inner join query to extract only those sales orders that have line items:
first sod_det where
so_nbr=sod_nbr
so_domain=sod_domain
You use inner joins to query fields and tables in other business objects in your system that contain other types of data you require—address information, for example—not related to the current data object.
first ad_mstr where
ad_addr=so_cust and
ad_ctry="US" and
ad_domain=so_domain
You combine multiple inner join queries by using a comma to separate the queries, as shown here:
first sod_det where
so_nbr=sod_nbr
so_domain=sod_domain,
first ad_mstr where
ad_addr=so_cust and
ad_ctry="US" and
ad_domain=so_domain
In this example, the query first searches the sod_det table in the current business object and identifies those sales orders that have line items. Then the query searches the ad_mstr table—this table could be in a different data object in a different domain—and returns the rows that have customers that are located in the US. When specifying inner joins, all query conditions you specify must be true in order for the query to extract data.
When QXO first connects to a source application with no domains and then connects to another source application with domains, and these two source applications are registered to one event service, you must add a prefix—qaddb—to the table name in the inner join query; otherwise, the inner join will not work properly. For example, in the abovementioned scenario, you must use qaddb.ad_mstr instead of ad_mstr in the inner join query.
first qaddb.ad_mstr where
ad_addr=so_cust and
ad_ctry="US" and
ad_domain=so_domain
Note: If you want to use the Query Service on business objects that have filters and/or inner joins as part of their definition, you can control whether these conditions remain active in the request QDoc by setting the IgnoreBOFilter and IgnoreBOInnerJoin nodes as required. These nodes take logical values. For details about the Query Service, see QXtend Query Service.