Offline Queries

Query data is sent to the client as part of the initialization process. This can lead to the data becoming stale when data in dependent entities change but the query data does not. To avoid this offline queries can be used which retrieve their data from offline entities.

An offlinequery property can be added to queries in the projection file.

query InventoryPartLocationsInStock {
    syncpolicy None;
    from = "Inventory_Location14";
    offlinequery = from InventoryPartLocation ipl
                   where ipl.QtyAvailable > 0
                   select distinct ipl.Contract, ipl.PartNo, 
                      ipl.ConditionCode, ipl.LocationNo, ipl.LocationDescription;

    keys = Contract, PartNo, ConditionCode, LocationNo;

    attribute Contract Text;
    attribute PartNo Text;
    attribute ConditionCode Text;
    attribute LocationNo Text;
    attribute LocationDescription Text;
}

• syncpolicy must be set to None since there will be no data sent to the client.
• keys must be specified

The select is last within the query to allow improved tooling support. Declaring the from at the beginning allows tools to know which tables and attributes can be used in the select. This also allows easy recognition of a query as either a SQL query or a Marble query.

If a procedure tries inserting, updating or deleting to the query from the offline file an error will be thrown.

From

from <EntityName> <alias>

from InventoryPartLocation ipl

• The from clause is mandatory
• Specifying an entity set is not allowed
• An alias must be provided
• The entity must be backed by offline data - no online entities are allowed.

Join

[left] join <EntityName> <alias> on <expression>

from InventoryPartLocation ipl
join Company c on c ipl.Company = c.Company
join CompanyPart cp on ipl.PartNo = cp.PartNo and c.Company = cp.Company
left join User u on ipl.PartOwner = u.UserId

• Joins are optional
• Specifying an entity set is not allowed
• An alias must be provided
• The entity must be backed by offline data - no online entities are allowed.
• join is an INNER join
• left join is a LEFT OUTER join

Where

where <expression>

where ipl.QtyAvailable > 0

where p.PartSize in ("MEDIUM", "LARGE")

where ipl.PartRef.PartSize = "MEDIUM"

• Where is optional
• Supports the same operators as offlinefilter
• Nested queries using in and exists are not supported
• References from entities can be used and will add an implicit LEFT OUTER join to the query.

Select

select [distinct] <Attribute> [as <Alias>], ...

select distinct ipl.LocationNo, ipl.LocationDescription

select ipl.Contract, ipl.PartNo

select ipl.LocationNo as LocNo, ipl.LocationDescription

select ipl.PartNo, ipl.PartRef.PartSize as PartSize

• The select clause is mandatory
• The distinct clause is optional. When added duplicate results will be not be included.
• References from entities can be used and will add an implicit LEFT OUTER join to the query.

The select attributes are matched up by name to the attributes defined on the query. The offline query must return the same attributes as defined in the query. The as keyword can be used to rename a select attribute to match the query attribute.

Examples

// Find all parts that are not INVALID using left join
from InventoryPartLocation ipl
left join Part p on ipl.PartNo = p.PartNo
where p.Name != "INVALID"
select ipl.Contract, ipl.PartNo, p.Name as PartName, ipl.ConditionCode, 
       ipl.LocationNo, ipl.LocationDescription;

// Find all parts that are not INVALID using entity reference
from InventoryPartLocation ipl
where p.PartRef.Name != "INVALID"
select ipl.Contract, ipl.PartNo, p.PartRef.Name as PartName, 
       ipl.ConditionCode, ipl.LocationNo, ipl.LocationDescription;

// Find all distinct locations where there parts
from InventoryPartLocation ipl 
select distinct ipl.LocationNo, ipl.LocationDescription;