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 specifiedThe 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 <EntityName> <alias>
from InventoryPartLocation ipl
from
clause is mandatory[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
join
is an INNER
joinleft join
is a LEFT OUTER
joinwhere <expression>
where ipl.QtyAvailable > 0 where p.PartSize in ("MEDIUM", "LARGE") where ipl.PartRef.PartSize = "MEDIUM"
offlinefilter
in
and exists
are not supportedLEFT OUTER
join to the query.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
select
clause is mandatorydistinct
clause is optional. When added duplicate results will be not be included.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.
// 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;