How To Create A Quick Query In IQ Objects

What is a Query?In simplest terms, a query is a request for information. A query tells IQ/Objects

what to look for in your database, how to format the data and where to send it. You construct queries by using

different tools to choose fields, apply filters and format the output. The instructions in your query are stored

in a design document. Design Documents IQ/Objects uses a design document to hold your query. (In the older version

of IQ this was called a “stored procedure?.) The design document holds the instructions for running one or more

queries against your database. These documents can be saved so they can be run again, used as part of another

query, or transferred to another IQ/Objects user. Design Windows You can create a design document in one of two

types of design windows: the Quick Query window or the FreeForm window. The primary difference between the Quick

Query and FreeForm windows is the tool set – there are lots of tools in the FreeForm window, and very few in the

Quick Query window. If you begin a query in Quick Query and find that you need more options, you can transfer to

the FreeForm window and continue your query without starting over. The Quick Query window This window contains a

set of tools for selecting, grouping, sorting and formatting data. It produces simple queries that show you

results quickly and efficiently. As soon as you select data columns, the data appears in the Quick Query window in

a spreadsheet-like format. This is ideal for finding information with a minimum of effort. Only Quick Query design

is covered in this user guide, but many of the same tools are also available in FreeForm.The FreeForm window

Queries can be expanded and reformatted using FreeForm. A large tool set exists in this window to allow data to be

presented in almost any format. FreeForm allows the use of different document sections such as page headers and

footers, document headers and footers, an expandable detail area, and more. Unlike Quick Query, FreeForm lets you

lay out your document in whatever format you wish, without restricting you to a columnar layout. Different kinds

of objects (pictures, text, data columns, user prompts and more) can be used in the FreeForm environment to build

a very sophisticated query document. Queries started in the Quick Query window can be transferred to the FreeForm

window. FreeForm query design is covered in a separate user guide.

Accessing IQ/Objects Quick Query Starting IQ/Objects IQ/Objects can be accessed by the following methods: From a

shortcut:If there is a shortcut icon on your desktop for IQ/Objects, double-click the icon to start IQ/Objects

From the Sigma program group:Click Start > Programs. Look for a program group for IQ; if it does not exist, click

Sigma Systems and look for IQ/Objects in the Sigma program group. Click IQ/Objects to load the program. Accessing

Quick Query To start a new Quick Query document, click File (on the menu bar) > New and select Quick Query. The

New Document dialog box appears. Click the radio button for View as: Quick Query. Check the Use Wizard box if you

wish to use the Query Wizard, or uncheck the box to skip the Wizard. (The Query Wizard will be covered later in

this user guide.) Click the OK button to continue. Notes: if the Query Wizard appears and you do not wish to use

it, click the Cancel button. IQ/Objects will start automatically in “Quick Query mode? if a Quick Query was the

last thing you did before previously exiting the program, or if it is your default document window. you can

specify how IQ/Objects starts each session by using the Preferences dialog box to set the defaults.

The Quick Query document window When you select New Quick Query document, the Quick Query window appears. The

various parts of the window are identified in the illustration below. the Menu barObject Directory “grip

lines?Tool bars the Main Object Directory – the RPIS data types are listed alphabeticallyyour Quick Query results

appear here You can resize the Object Directory by dragging the border to the left or right the Status barthe menu

bar: the menu bar groups similar options together under a descriptive menu heading. Many of the options available

in the menus are also available as buttons on a toolbar. You can select the option either by clicking on the tool

icon or by opening the appropriate menu and choosing the option. the tool bars: various options are available in

Quick Query as icons. These icons are called tools, and they reside on a toolbar. Click the tool to select it. If

you move the cursor over a tool and let it rest there for a few seconds, a “tool tip? will pop up to explain what

the tool does.the Object Directory: the RPIS data types are displayed as folder icons in this window. The data

types, or objects, are listed alphabetically. Double click a folder to open it and display the contents; the data

columns are also displayed alphabetically. You can have the Object Directory “float? on top of your query so that

your data will expand to use the entire window, or you can leave the Object Directory “docked? (attached) to the

top, bottom, right or left edge of the document window. The “grip lines? allow you to float or dock the directory.

grip lines
To make the Object Directory float: double-click the “grip? lines – the two gray ridged lines. You can drag the

Object Directory to any part of the screen by grabbing its title bar. It will float over your data until you dock

it again. To dock the Object Directory, use one of these methods: Drag it to the top, bottom or either side of the

window. A shadow outline will appear where the Object Directory will dock; drop it when you are satisfied with the

proposed location. Double-click the Object Directory’s title bar – it will return to its most recent docked

location. the status bar: the status bar appears at the bottom edge of the IQ/Objects window. It displays

information about your query such as the current page number and the total number of pages. It also provides

navigation buttons so you can scroll through your data. You can select a data value by clicking it – the data will

be highlighted to indicate that it’s been selected first prev next last record record record record the status bar

Notice the selected data value is on the 5thline, so the status indicator says “Record 5?
Creating a Quick Query Selecting Columns The first step in creating a query is to select columns from your

database. You can “click and drag? columns from the Object Directory into your document or create custom columns

using arithmetic functions. To select and place columns follow these steps: 1. In the Object directory,

double-click on a data type to display its contents. A list of columns will appear underneath the folder icon for

that data type. The fields are displayed alphabetically. the Object Directory to open the Parcel data type, double

click on the Parcel folder 2. Choose a column from the list; use the scroll bar on the right side of the Object

directory to move up and down the column list until you find your choice. 3. Click on the column name and drag it

into the document window. The cursor will change to a folder with a plus sign to indicate the intention to add a

column to your query. If you drag the column onto a toolbar or other non-query part of the screen, the cursor will

change to the international “No? symbol ( ) to indicate that you cannot drop a column in that location. Columns

are added to your query from left to right.

Drag the desired column into the document window and drop it – the column appears and data is displayed. 4.

Continue selecting, dragging and dropping columns until you have all the data you need. If the width of the data

columns exceeds the window size, a scroll bar will appear at the bottom of the window to allow you to view all the

data. Moving Columns To move a column to a new location in your query: Click the column header to select the

entire columnClick the column header again and hold down the mouse key as you drag the column to a new location. A

colored vertical grid line will move with the mouse to indicate where the column can be inserted. When you reach

the desired new location, release the mouse key. The column will move to its newposition. Deleting ColumnsTo

delete a column from your query: Click the column header to select the entire columnPress the Delete key. The

column will be removed from your query. Formatting Columns You can format your data using standard Windows

formatting tools including Bold, Italic, Underline, Font Type and Font Size. You can also add color to your text,

center it, or right- or left-justify it. Click the column you wish to format. Click a formatting tool to apply a

new format to the column. fontsize bold-italic-underline justification color.

Changing a Column Header The default column name comes from the database. If you wish to assign a more descriptive

header to the column, you can change the name. Double-click the header of the column you wish to change. The

Column dialog box appears. The current column name is displayed in the Object Label box at the top. Object Label

Type a new column name in the Object Label box. Click OK. Adding a Custom Column You can create custom columns

(columns that do not exist in your database) to your query. Custom columns are useful for applying mathematical

functions to your data and displaying the results in a query. On the Menu bar, click Insert > Custom OR click the

Custom icon. The Custom dialog box appears: Type your custom column name in the Object Label boxUse the calculator

keypad to build your formula Type your formula in the Value box listObject directorySelect an Aggregate Function

like Sum or Average from this.

Select a data column from the object directory to use in your formula. If the column you wish to use is already in

your query, click the Objects button and open the Detail folder to see the columns. If you wish to use a database

column that doesn’t appear in your query, click the Columns button to select from the full list. Double-click a

column to select it, or drag-and-drop the column inside the Value box. Selecting from database columns Selecting

from query objects Use the calculator pad to add arithmetic functions to your formula or simply type the

characters into the Value box. To insert a function such as substring (SUBSTR), SUM, MIN, MAX or other basic

functions, click the Function button and select a function. You can edit the function in the Value window. When

the formula is complete, click the OK button. Custom columns, formulas and functions are covered in more detail in

the Advanced IQ/Objects User Guide.

Sorting Your Query Quick Sort You can quickly sort your data by any column (or combination of columns) in your

query: Click the column header of the column you wish to sort by. To select more than one column, hold down the

Ctrl key while clicking with the mouse. To sort in ascending order, click the Ascending Sort icon (A-Z). To sort

in descending order, click the Descending Sort icon (Z-A). Alternative method: Right-click in one of the

highlighted columns and select Sort from the popup menu. Click Sort on the popup menu Sorting in a Specific Order

For a more complex sort, use the Sort Order/Filter dialog box: On the menu bar, click Query > Sort Order. The Sort

Order dialog box appears. columns in the Non Ordered Objects box are not part of the sorting instructions columns

in the Ordered Objects box will be used to sort your data change the sort order of a column from ascending to

descending by clicking this button

All the columns appearing in your query will be initially listed in the left-hand Non Ordered Objects box;

highlight a column and click the right arrow button (or double-click the column) to move it to the right-hand

Ordered Objects box. All columns in the Ordered Objects box will be sorted in the order in which they appear. To

change the sort order, select a column and use the up/down arrows to the right of the Ordered Objects box to move

the column up or down the list. All sorts are done in ascending order by default. To change a column to descending

sort order, select the column and click the Asc./Des. button. (You must select and change each column separately.)

When you use a sort, either by the “quick select? method or by using the Sort dialog box, the sort-by columns

remain active until you delete the sort instructions from your query. If you use the quick select method and then

open the Sort dialog box, your quick sort columns will appear in the Ordered Objects box. Removing a Sort To

remove a sort and return your data to its original order, follow the steps below. On the menu bar, click Query >

Sort Order. The Sort Order dialog box appears. click to select a column and use the left arrow button to move the

column to the Non Ordered Objects box Select a column to remove from your sort. Click the left arrow button to

return the column to the Non Ordered Objects box. When all columns have been returned to the Non Ordered Objects

box, the sort instructions will have been removed and your data will appear in PARCEL-ID order by default. Note:

because PARCEL_ID is the default sort, assigning a new sort by PARCEL_ID will also return to data to its original

state.

Filtering Your Query When you build a query, all of your data appears in the design window by default. You can

apply filters to the data to screen out any parcels you wish to exclude. If you want to see all data again, you

can delete the filter conditions. Types of Filters IQ/Objects has three types of filters available: base, ad hoc

and quick. All filter types do the same thing – they instruct IQ/Objects to narrow the focus of your query so only

the data you wish to see appears in the document window. The filter types differ in terms of application – you use

each type for a different reason. You can apply the different types of filters in combination, but be careful – if

one filter type has a condition that conflicts with a condition in another filter type, you may not get the

results you expect or want. The three filter types are described below. Base Filter Every query document has a

base filter. When you begin a query document, the base filter is empty (it contains no conditions.) When you save

the document, the base filter is saved with the query and will be applied each time the query document is opened.

This is useful for defining the factors that must always be true for data to be included in the query, such as

including only data with the appropriate parcel year or community ID number. The base filter always applies unless

you delete it in the Filter/Sort dialog box. Defining a Base Filter On the Menu bar, click Query > Filter > Edit.

(If the Sort tab is on top, click the Filter tab) or click the Filter icon on the toolbar. In the Filter Types

list, click the radio button for Base. (Note: if the radio button for Current is checked, all conditions will be

added to the base filter by default.) filter typeClick the “Base? radio button to change theDefine the filter

conditions (see the “Editing Filter Conditions? section for detailed instructions) and click the Add button. The

base filter is added to the query. Click OK to apply the filter and close the dialog box. Removing a Base Filter

On the Menu bar, click Query > Filter > Edit. (If the Sort tab is on top, click the Filter tab) or click the

Filter icon on the toolbar. Click once on the filter statement to select it. The Base filter type radio button

should be checked. Click the Delete button to remove the filter.

Ad Hoc Filters Filters that are created “on the fly? as you work with a query are called ad hoc (pronounced “add

hock?) filters. Any data value displayed in your query can be used as the basis for an ad hoc filter. The more

filters you add, the narrower the focus of your query. Ad hoc filters are saved with your document and will be

applied automatically the next time you open it. They can also be deleted at any time, or edited using the

Filter/Sort dialog box. Defining an Ad Hoc Filter Select a data value in your query – for example, if your query

includes the STATE_USE_CD column and you wish to see only parcels with a use code of 130, find a parcel (row) with

a use code of 130 and click the cell containing the value “130?. Click Query > Filter > Apply on the menu bar or

click the Apply Filter icon on the toolbar. Note: If the data type to be used in your filter is numeric, the

Filter Operator Selection dialog box will appear. Choose the operator you wish to use from the drop down menu (=,

>, <, etc.) then click OK to apply the filter. Removing an Ad Hoc Filter Click Query > Filter > Clear on the menu

bar or click the Clear Filter icon on the toolbar. Quick Filters Quick filters are filters that have been given a

name and saved. This feature is most useful for creating a number of named, single-condition filters that can be

applied in various combinations whenever you need to narrow the focus of your query. Quick filters are always

applied in addition to the base filter conditions (remember that base filter conditions are permanently attached

to the query until you delete them.) If any ad hoc filters exist, they will also be applied to the query. Defining

a Quick Filter (optional) Clear any currently applied ad hoc and quick filters from your query by clicking the

Clear Filter icon on the toolbar or by clicking Query > Filter > Clear. Enter or edit filter conditions for the

base filter if you wish to make it part of the quick filter. Add the ad hoc filter conditions you wish to make

part of the quick filter. Add one or more existing quick filters: click the Quick Filter icon on the toolbar ( )

or click Query > Quick Filters, then select the filters to apply and click OK. (You may select multiple filters by

holding down the Ctrl key and clicking the filter name.) On the menu bar click Query > Filter > Save As and type a

name for your quick filter, then click OK. All current filter conditions will be saved as a named quick filter.

Notes About Quick Filters When you choose to name and save the current filter as a quick filter, ALL filters

currently in effect are saved under that name (base, ad hoc and quick.) If no filters are in effect when you

create a quick filter, it will be initially empty, but you can modify it later to add filter conditions. If the

same condition exists in an ad hoc filter and a quick filter, the condition will be applied twice. An existing

quick filter can be renamed: click the Quick Filter icon or click Query > Quick Filter, then select the quick

filter to be renamed and click the Rename button. Type a new name and click OK. Click OK to close the Quick Filter

window. Editing Filter Conditions Any type of filter can be created or modified in the Filter/Sort dialog box. You

can also use this box to delete base and ad hoc filters; you must use the quick filter dialog box to remove or

rename a quick filter.To access the Filter/Sort dialog box, click Query > Filter > Edit on the menu bar or click

the Filter icon . If the Sort tab is active, click the Filter tab to change to filter options. The Filter tab is

described below. The Filter/Sort Dialog Box Click the Filter tab to move the filter options in front of Sort if

necessaryCreate or edit filter conditions in this area e)Filter conditions applied to your query appear in this

area (conditions are preceded by the filter typClick a filtertype to see its current filter conditions (Click

Current to see all filter conditions) The Distinct Values Only box does not apply to filters; it is used to group

data so that no combination of data values appears more than once. Leave this box unchecked for a normal query

When building a filter condition you can select Objects from your query or choose any other Column from your

database

Changing an existing filter Existing filters can be changed in the Filter/Sort dialog box. Click the filter

statement you wish to modify to select it, then change the statement in the “Select Query Data When…? area. In

this example, the existing filter selects only those parcels with a use code of 130. We’ll change the filter to

select parcels with a use code of 101. To edit an existing filter, click Query > Filter > Edit on the menu bar or

click the Filter icon . If the Sort tab is active, click the Filter tab to change to filter options. Our example

filter looks like this: The existing filter appears in the current filter list – click once on the filter

statement to select it. The filter will appear in the “Select Query Data When…? area, where you can modify the

object, the operator and the value(s) To change the use code in the filter statement from 130 to 101, follow these

steps: 1. Click once on the existing filter statement to select it. The statement is loaded into the “Select Query

Data When…? area at the top of the sheet. 2. Change the use code by clicking in the Value1 box, deleting 130 and

typing in 101. 3. Click the Modify button to place the revised value in the filter statement.

To change the use code from 130 to 101, type the new use code into the Value1 box Click the Modify button to

accept the change;the old filter statement will be replaced and the new use code value will appear in the revised

statement 4. Click the OK button to apply the new filter to your query. Creating a Filter Using the Filter Dialog

Box The easiest and fastest way of filtering data is by creating an ad hoc filter, but in some cases you may wish

to use the Filter dialog box instead. The two primary reasons for using the Filter dialog box are: to create a

complex filter that uses both AND and OR operatorsIQ/Objects looks at all filter conditions and automatically

determines whether they should be combined using AND or OR. You can change the groupings manually if you wish to

override IQ’s choice. to create a filter using a data column that doesn’t appear in your queryAd hoc filters can

only be created by using values that appear in your query. You can base a filter on any field in your database by

creating or editing the filter in the Filter dialog box instead. Using AND and OR Conditions in a FilterIQ/Objects

will automatically use the conditions AND/OR whenever it sees multiple statements in your filter. In general,

IQ/Objects uses these rules in assigning AND and OR to your filter combination: if your filters refer to the same

column and use “equals? (=) with a value for comparison, IQ/Objects will combine the filters using OR (see example

1 below.)

if your filters refer to the same data column but use an operator other than the equals sign to compare two

different values (such as >, <, <=, “not null?, etc.) against the same data, IQ/Objects will combine the filters

with the condition AND (see example 2 below.) if your filters refer to different data columns IQ/Objects will

combine the filters with the condition AND (see example 3 below.) Example 1 – Filters referencing the same data

column, using the equals sign This query is using two filters (one ad hoc and one quick filter named “USE101?.)

Because they use the same data column (STATE_USE_CD) and use the equals sign to compare two different values (130

and 101) against the same data column (STATE_USE_CD), IQ/Objects will combine the filters with the condition OR:

Select Query Data When STATE_USE_CD = 130 OR STATE_USE_CD = 101. Example 2 – Filters referencing the same data

column, not using the equals sign This query is using two filters (an ad hoc and a base filter.) Because they use

the same data column (STATE_USE_CD) but do not use the equals sign to compare two different values (133 and 129)

against the data column (STATE_USE_CD), IQ/Objects will combine the filters with the condition AND: Select Query

Data When STATE_USE_CD < 133 AND STATE_USE_CD > 129. (The resulting query would show only parcels with use codes

130, 131 and 132.)

Example 3 – Filters not referencing the same data columnThis query is using two ad hoc filters. Because they

reference different data columns (PARCEL_YEAR and STATE_USE_CD), IQ/Objects will combine the filters with the

condition AND: Select Query Data When PARCEL_YEAR = 2001 AND STATE_USE_CD = 101. Using “OR Groups? When you use

complex filter combinations you may wish to override IQ/Objects’ automatic AND/OR assignments. You do this by

dividing the filter statements into groups and then assigning numbers greater than zero (0) to the groups that

need to be combined with other statements using OR. Conditions that should remain connected by AND are assigned an

“OR Group? number of zero (0). Example: Use this box to change the OR group number of a filter statement The

highlighted filter statement and the statements below it are assigned an OR Group number of 1. They will be

grouped together and considered as one filter statement In this example, IQ Objects would apply the filters in

this combination: Select Query Data When (PARCEL_YEAR = 2001 AND STATE_USE_CD > 129) OR (PARCEL_YEAR > 2001 AND

STATE_USE_CD >= 300 AND STATE_USE_CD < 400)

Filters Using a Column Not in the QueryA filter can reference a data column that will not appear in your query.

You must build the filter statement in the Filter dialog box; you cannot create an ad hoc filter if the column is

not available in the document window. To build a filter statement using a column not found in the query: click

Query > Filter > Edit on the menu bar or click the Filter icon . If the Sort tab is active, click the Filter tab

to change to filter options. click the Columns button in the Object area; a list of data tables will appear.

double-click a table name to display its data columns double-click a data column to place it in the Object box in

the “Select Data…? area select an operator and enter the desired value(s) to compare against the data columnclick

the Add button to add the statement to the filter click the OK button to apply the filter. Enter the desired

operators and values for the filter Remember to click the Add button to add the statement to your filter – the

button has not been clicked yet in this example, so the statement does not appear in the condition box Click the

Columns button and select a table and data column from the

Saving A Design Document Saving the design document will allow you to run the query again at a later time without

having to redesign the query from scratch. IQ/Objects does not save the data produced by the query – it just saves

the instructions. When you open a saved design document, the query will again be run against the database as if

you had just designed it. How to Save a Design Document Click File > Save on the menu bar or click the Save icon .

The Save dialog box appears: Type a name for your design document in the File Name box and click the Save button.

Saving a Template You can save a design document as a template (a model for future design documents.) You must

click File > Save As on the menu bar to save a design document as a template – do not use the Save icon. Click

File > Save As; the Save As dialog box appears. Using the “Save as type? pull-down menu, select Columnar [*.iqc].

Type a file name for your design document template in the File name box. Click Save to save your template. To open

a saved query template: Click File > New From Template. Select Columnar [*.iqc] from the Files of Type pull-down

menu. All existing template files will be listed in the central box. Click on the desired template name and click

the Open button, or simply double-click the template nameto open it.

Using the Query Wizard The Query Wizard is a tool that walks you through the process of building a query. It

displays a dialog box for each step of the process and builds a query based on the choices you make in each box.

Starting the Query Wizard There are two ways of starting the Wizard:On the menu bar click Query > Use Query Wizard

In the New Document dialog box, check the Use Wizard box before clicking OK. Check the Use Wizard box to build

your new query with help from the Wizard The Wizard will walk you through the necessary steps (selecting columns,

sorting data, filtering data, etc.) in a logical order. Answer the questions and make selections according to the

instructions displayed in each dialog box, then click the Next button to continue to the next step. You can go

back a step by clicking the Back button, or exit the Wizard at any time by clicking the Cancel button.

Using Online Help IQ/Objects has a comprehensive help system available to assist you. To access the help system:

Click Help > Contents on the menu bar, or Click the Help button on any dialog box. Clicking Help > Contents will

launch the online help system and display the Contents list: Click a topic to see more information If you’re not

familiar with online help, click the Hints for Using Help topic Click the Stop Sign to exit Help or click File >

Exit Click the binoculars to bring up the search index Each topic appears as a hyperlink. Click on a topic to be

connected or “linked? to information about that topic. Links appear underlined, just as they do when you use a Web

browser such as Internet Explorer or Netscape Communicator.Click the Back button to back up to the previous page.

Click the forward/back icons ( << and >>) to navigate forwards and backwards through the help pages. Clicking the

binoculars will bring up an indexed list of all help topics. Type the first few letters of the topic you wish to

learn about in the top box; matching data topics will appear alphabetically in the bottom box. The example below

shows a search for topics related to the word “filter.? Click the Contents tab to see the list of general Help

Topics.Click File > Exit or the exit icon (X) in the corner of the help window to exit online help and return to

IQ/Objects.

Type a word or part of a word here Double-click on a help topic to view the information Click the Contents tab to

see a list of general help topics