1.5.2. Label Report Query Definition

1.5.2. Label Report Query Definition
	1.5. Report Definition Page Setup

1.5.2. Label Report Query Definition

The query in the report definition is sophisticated and yet fairly straight forward. It is shown below:

SELECT sequence_value, cohead.cohead_number, cohead.cohead_shipto_id,
       cohead.cohead_custponumber, shipto.shipto_name, shipto.shipto_address1,
       shipto.shipto_address2, shipto.shipto_address3, shipto.shipto_city,
       shipto.shipto_state, shipto.shipto_zipcode
FROM public.cohead cohead, public.shipto shipto, public.sequence
WHERE cohead.cohead_shipto_id = shipto.shipto_id
  AND ((cohead.cohead_id=<? value("sohead_id") ?>)
  AND (sequence.sequence_value BETWEEN <? value("labelFrom") ?> AND <? value("labelTo") ?>));

Let's take a look at each section of this query:

SELECT Section

This portion of the SQL retrieves column values from three tables: sequence, cohead, and shipto. The values retrieved are used to define the columns in the Property (Fields) session which controls what and how dynamic information is displayed on the label. The descriptions of these fields are self explanatory.

FROM Section

This portion of the SQL specifies the tables from which the query retrieves the data:

Table 5.7. Tables used in FROM section

`cohead`	This table contains header information for sales orders.
`shipto`	This table contains customer shipping addresses.
`sequence`	This table contains a sequence of numbers from 1 to 1000 and facilitates the execution of the SQL multiple times in order to generate multiple labels.

WHERE Section

The WHERE section of the SQL does the following:

Retrieves the row in the table cohead where the column cohead_id equals the value for the parameter passed from the user (the user enters the order number but the program passes the order's system generated cohead_id).
Joins the tables cohead and shipto on the columns cohead_shipto_id and shipto_id.
Causes the SQL to fire multiple times to print multiple labels. The table sequence contains a sequential list of integers from 1 to 1000 and is used by the query such that it repeats for the number of times contained in the range defined by the labelFrom and labelTo parameters. The information returned is the same each time but by design the report writer generates one label each time the SQL returns a row of information.

The table shipto contains the specific the shipping address information that appears on the label. Also note that parameters are contained inside the MetaSQL tags <? and ?>.

Note

Finally, for the sake of simplicity, this sample label's SQL only generates labels for orders in which the shipping address is selected from the list of ship-to's by customer. In this case this scenario the shipping address is contained in the table shipto. For orders where the ship to address is merely copied using the COPY TO SHIP-TO -> button, the address information is contained in the table cohead. A more sophisticated query, leveraging MetaSQL could test for this condition (the value of column cohead_shipto_id = -1 in table cohead) and retrieve the shipping information directly from cohead. If you decide to re-create this label, make certain to test it against a sales order that uses an address from the list of pre-defined customer ship-to's.


1.5.1. Displaying a Parameter Value		1.5.3. Linking Label Name to Report Definition

OpenRPT Product Guide

1.5.2. Label Report Query Definition

Note