Coins ERP+ uses a simplified version of the Progress 4GL query language in combination with RSP's (Record Service Procedures) to extract the data for reports and enquiries (for further information on RSP's– see later in this guide).
Coins ERP+ uses the query to decide which records are accessed from the coins database from the database. In response to a query, the database returns a result set, which is just a list of rows containing the answers. The Page/Report Design will determine which fields from these records are displayed (either on screen or in a report).
The simplest query is just to return all the rows from a table, but more often, the rows are filtered in some way to return just the answer wanted.
The flexibility of relational databases allows programmers to write queries that were not anticipated by the database designers. As a result, relational databases can be used by multiple applications in ways the original designers did not foresee, which is especially important for databases that might be used for decades. This has made the idea and implementation of relational databases very popular with businesses.
FOR EACH
To begin a query in Coins ERP+, the first statement must begin FOR EACH followed by a table name.
Example Query on the coins database to retrieve all contracts (jc_job)
FOR EACH jc_job
jc_job is the name of the table in the Coins ERP+ database
The FOR EACH statement starts a block of code that iterates once for each contract record (hence the syntax FOR EACH)
WHERE
Simply specifying the table with a FOR EACH statement in a query is okay, assuming we want every record from the selected table, but in practice we would normally want to restrict the number of records returned in some way. In Coins ERP+, transactional data is held at company level.
Even though you may only have one company in your organisation, the data is still recorded with a company identifier. Coins ERP+ uses the field kco to identify the company number.
Most queries will need to specify the kco values to ensure that the records returned relate specifically to the company you are reporting on.
The WHERE statement is used to add a constraint to the query and may refer to a constant, filed name, variable name or expression whose value you want to use to select records
Example Query on the coins database to retrieve all contracts (jc_job) that belong to company 1:
FOR EACH jc_job WHERE kco = 1
In the example above we have used ‘=’ as the comparison operator. There are a number of others than may be used with the WHERE statement. These are listed in the table below:
Keyword | Symbol | Description |
EQ | = | Equal to |
NE | <> | Not equal to |
GT | > | Greater than |
LT | < | Less than |
GE | >= | Greater than or equal to |
LE | <= | Less than or equal to |
BEGINS | Not applicable | A character value that begins with this substring. |
MATCHES | Not applicable | A character value that matches this substring, which can include wild card characters The expression you use to the right of the MATCHES keyword can contain the wild card characters: An asterisk (*) represents one or more missing characters. A period (.) represents exactly one missing character. |
CONTAINS | Not applicable | A database text field that has a special kind of index called a WORD-INDEX The WORD-INDEX indexes all the words in a field’s text strings, for all the records of the table, allowing you to locate individual words or associated words in the database records, much as you do when you use an Internet search engine to locate text in documents on the web.. |
The WHERE statement can be followed by any expression that identifies a subset of the data using AND/OR to join multiple tests.
Example Query on the coins database to retrieve a specific contract (field job_num) for Company 1 from table jc_job
FOR EACH jc_job WHERE jc_job.kco = 1
AND jc_job.job_num = '123456'
Joining Tables
Often, data from multiple tables gets combined into one, by doing a join. Conceptually, this is done by taking all possible combinations of rows (the "cross-product"), and then filtering out everything except the answer.
To begin each join a comma should end the previous statement before beginning the next one. DO NOT add a comma to the end of the last statement as this will result in an error.
EACH
FOR is only used for the first table in the query, all subsequent tables must be accessed with EACH to start an iterating query that will find a single record on each pass
To establish a join, the table(s) you are adding to the query must have some relation to one or more tables already in the query.
Example Query on the coins database to retrieve all costheads (jc_costcode) that belong to contracts (jc_job) that belong to the logged in Company
FOR EACH jc_job WHERE jc_job.kco = {kco},
EACH jc_costcode WHERE jc_costcode.kco = jc_job.kco
AND jc_costcode.job_num = jc_job.job_numIf you do not use the EACH keyword for a subsequent table then you must use one of the following to obtain a single record:
FIRST
Uses the criteria in the record-phrase to find the first record in the table that meets that criterion.
Progress finds the first record before any sorting.
LAST
Uses the criteria in the record-phrase to find the last record in the table that meets that criterion.
Progress finds the last record before sorting.
The FIRST and LAST keywords are especially useful when you are sorting records in a table in which you want to display information. Often, several related records exist in a related table, but you only want to display the first or last related record from that table in the sort. You can use FIRST or LAST in these cases.
OF
Some of the tables in the Coins ERP+ database share a relationship based on common field names between record and table that also participate in a UNIQUE index for either record or table. All OF relationships within the coins database are detailed in the database enquiry and appear for each table in the form similar to:
Where such a relationship exits, the OF statement may be used to relate one table to another. So in our earlier example we used the query:
Copy
FOR EACH jc_job WHERE jc_job.kco = {kco},
EACH jc_costcode WHERE jc_costcode.kco = jc_job.kco
AND jc_costcode.job_num = jc_job.job_numAn OF relationship exists between jc_job and jc_costcode as can be seen in the database enquiry for jc_job:
So we can re-write this query as:
Copy
FOR EACH jc_job WHERE jc_job.kco = {kco},
EACH jc_costcode OF jc_jobCurly Braces
The functionality of {}'s is to specify a place holder in fields and calculations into which a value can be passed.. When using {}'s around a field the use of quotes is required if the field is a character field. The use of double or single quotes is acceptable.
The only thing to be aware of is that when using '{field}' replacement on a character field is that if the information within the field could contain an apostrophe (for example- J O'Connor) then the apostrophe would cause close to the single quote and you will get a symbol not found(Connor) Error. To overcome this error the use of double quotes "{field}" is the answer.
The use of {}'s in calculations is possible on all field values except within the DataSets and the calculate conditions on a report. In these instances it is necessary to always qualify out the field with the table name.
{RO_ContractCosts^TD|0|{RS_glp_fdate__2}}
would be written as:
jc_job.RO_ContractCosts^TD|0|{RS_glp_fdate__2}.
The use of the table name is allowed in all calculations but whereas in most instances the formatting of the result is suppressed, within the calculate condition it is not and therefore the comma in a result of a figure in excess of 1,000 may result in an error in syntax in a calculation. (NB. Please note that the replacement on parameters of an RO field is still acceptable).
Within the Coins ERP+ reporter/screens we use curly braces {} as a method to pass values to a query or a report or a page. Enclosed within the curly braces you specify the commands, RS_fields, or other data you need to communicate across or within objects. {kco} is a common usage, and is used to place the current logged in company number into the query.
The next example gets information from jc_job and inherits the Company Number from the system, retrieving the company number the user is logged into.
FOR EACH jc_job WHERE jc_job.kco = {kco}
Outer-Join
Specifies a left outer join between record and the table (or join) specified by the previous Record phrase(s) of an OPEN QUERY statement. A left outer join combines and returns data from the specified tables in two ways.
First, the records selected for the table (or join) on the left side combine with each record selected using the OF or WHERE options from the table on the right (record). Second, the records selected for the table (or join) on the left side combine with unknown values (?) for the fields from the table on the right (record) for which no records are selected using the OF or WHERE options. The join is ordered according to the given sort criteria starting with the left-most table in the query.
If you specify the OUTER-JOIN option, you must also specify the OUTER-JOIN option in all succeeding Record phrases of the query to obtain a left outer join. That is, for multiple Record phrases, all joins in the query following your first left outer join must also be left outer joins. Otherwise, the result is an inner join for all records up to the last inner join in the query.
The OUTER-JOIN option is supported only in Record phrases specified after the first Record phrase in the query. If you specify OUTER-JOIN, you must also specify the OF option, WHERE option, or any combination of the OF and WHERE options. These options are required to select record (the right-most table) for the specified left outer join.
This query specifies a left outer join between customer and order, and also between that join and order-line. Thus, for each customer record that has no orders or has no orders with an order-num less than 50, the query returns the customer fields and ? for all fields of the order and order-line tables. In addition, if there are no order-line records with item-num less than 15 for any selected customer and order, the query returns ? for all fields of order-line. Otherwise, it returns each customer record along with its first selected order record and order-line record.
In all statements where multiple Record phrases are allowed, the default join (without the OUTER-JOIN option) is an inner join between record and the table (or join) specified by the previous Record phrase(s). An inner join returns the records selected for the table (or join) on the left side combined with each selected record from the table on the right (record). For an inner join, no records are returned for the table (or join) on the left for which no record is selected from the table on the right (record).
If you specify a Record phrase as an inner join, the current Record phrase and all preceding Record phrases in the query participate in contiguous inner joins, even if prior Record phrases specify the OUTER-JOIN option. Thus, for multiple Record phrases, all joins in the query up to the right-most inner join result in contiguous inner joins.
Buffers
There are circumstances when you may wish to access a single table multiple times in a query. The method for doing this is to use buffers in your query.
Using buffers in a Body Query.
The buffers MUST be named xxn<table> where n is a letter.
Example Syntax:
The buffers are automatically created (and deleted) and you can refer to fields using xxn<table>.field