Using XpertQuery™ as a Merchandise Selector
XpertMart™'s database is an open database which means that you as the user can make direct queries to the database. While XpertMart™ comes with many tools that are retrieving and organizing information from the database (reports, audit tables and the XpertMatrix™ are but three examples), you also have the power to access the database yourself, request specific information and display it in the order you want using the XpertQuery™ tool.
The Query by Example function,
very useful, is limited to extracting data from a single database
Using XpertQuery™ you can extract data from as
tables from the database as you'd like. (Every catalog
in XpertMart™ has a corresponding table in
database). XpertQuery™ lets you
these queries to the database using a graphical user interface.
You will use the XpertQuery™ tool throughout
system in a variety of ways. If you are searching through a catalog and
to employ a variety of search criteria that go beyond the scope of a
Query by Example then you can press the Query Button in the toolbar at the top of the catalog to open
XpertQuery™ and conduct your search.
Any time you are running a process and giving XpertMart™ a set of instructions such as exporting data,
mass changes to prices and costs,
or modifying large quantities of data using the Replace Where function, the
XpertQuery™ screen will usually open up as the
last step of the process.
This way you can use the XpertMart™ tool to
subsets of your merchandise that the instructions only apply to,
example, limiting the process to certain brands, departments or vendors.
You will also use the XpertQuery™ tool any time you want to modify or create a new report. Behind every report is a query to the database. You can view and modify this query by pressing the Query button which will open up the XpertQuery™ screen.
This chapter is only an introduction to the XpertQuery™
tool. To fully take advantage of all of the features
in XpertQuery™ prior knowledge of SQL is
Contact your XpertMart™ Authorized Dealer
you are intersted in learning more about SQL and the advanced features
The XpertQuery™ screen has a standard toolbar
the top and eight tabs. Each of these components is discussed below.
The XpertQuery™ toolbar has the following nine components:
New: Use this button to create a new query. When
you press this button
all of the data you have in the Tables, Link, Fields, Select,
Sort, SQL and Results
tabs will be erased. However, the information in the Database
tab will remain the same.
Open: Use this button to open a query
that has previously been saved.
You can then modify the query or use it as it is.
Save: Use this button to save your
query so that you can use it again
later. You will want to save any query you use repeatedly so that you
not have to reenter the information every time. Queries files are saved
a .qry extension.
Save As: Use this button to save the
query under a different name.
This option is particularly useful if you've modified a query and want
save it without overwriting the original query.
Execute: When you press this button XpertMart™
will execute the query, i.e. it will send the query instructions to the
and display the information you are requesting in the Results
tab. Executing a query is a lot like previewing the results. The actual
does not take effect until you've pressed the <OK> button
the bottom of the screen. You will want to execute the query any time
modified the query and want to see how the results change.
Maximum Rows: The value you enter
here is the maximum number of rows
of data that will be displayed in the Results
Usually you do not need to see every row of information, just enough to
whether your query is producing the kind of information you expected
you decide to press <OK>. If you need to see more rows at
time, enter a larger number in this field.
Description: You can enter a
description into this field that will
help you remember the contents or purpose of the query. Examples
"Items with Stock 0" or "May - July Sales".
Maximize Screen: increases the size of
the XpertQuery™ window to the full size of your
About: Displays information about
Each of the tabs in the XpertQuery™ get progressively more specific in the information you are wanting to retrieve from the database. The first step, of course, is to define the database you want to query. One of the advantages of the XpertQuery™ tool is that it allows you access and query multiple databases. (This way the same query can be used on different databases). However, unless you are an advanced user you will only be accessing one database, You will only need to configure this screen once.
If you are creating a new database connection, press the <New>
key on the right to clear out the fields and enter your data. If you
a mistake, you can always press the <Delete> key to start
Once you have filled in the five fields, press <Connect>
establish a connection to the database. You will know you are connected
you see the Tables tab full of information. If
failed to connect this tab will remain blank.
Connection: Enter a name or alias you want to use for this
connection. If you've saved more than one connection, press the arrow
to the right and the connection names will appear in a drop-down menu
can select from.
Driver: Specify the driver used (usually a Java class file)
access the database. If you are using InterBase the driver is
If you are using a different database manager check the documentation
the right driver to use.
Database: Enter the URL that the system needs to follow to
the database you are trying to access. NOTE: there is no restore button
you make a mistake entering this information and need to go back to
was saved before. Cut and paste the URL you are using into a text pad
making any changes so that you at least have a backup. The URL will
depending on whether you are on a network or not but will usually end
User Name: Enter the user name you registered with the database
when you first setup the database. The default value is SYSDBA.
Password: Enter the password you registered with the database
when you first setup the database. (If you followed our instructions during the Installation the password is "masterkey").
Save Password: If you select this checkbox then the password
be stored along with the information in the other fields. Otherwise
you choose this particular database connection you will need to
Comment: Use this to enter a description that will help you
the database you are connecting to, for example, "Southwest Apparel" or
Once you've made a connection to a database the next step is to pick the tables you want to query or extract information from. On the left side of the Tables tab you will see a list of every table included in the database you are connected to. Every catalog in XpertMart™ has a corresponding table in the database. The information you enter into the Styles catalog is entered into the "STYLES" table; the contents of the Taxes Catalog are stored in the "TAXES" table, and so on. You will also see some tables that XpertMart™ creates to store internal information used to run the system that is not always viewable by the user but (keeping with our open database philosophy) can also be exploited using XpertQuery™.
Most likely you will not need to use every table in the database in
query. To access a table, select the table from the list on the left
will appear highlighted in blue) and click on the Add button
. The Table will now appear on the right side of the screen, under
Tables." To remove a Table, select the table you want to remove and
on the Remove button
Note that sometimes you will need to include a
table in your query. A bridge table is a table you are not acessing for
but is needed to bridge two tables that are not linked together. For
suppose you are querying the database for all items
listed by vendor. The items table does not
vendor information. However, items are related to styles
that do have vendor information. So in this case you would want to
the STYLES table along with the VENDORS and ITEMS table to be able to
items to vendors. To learn more, see the Relational Nature of the Data topic.
Use the arrow keys in the top right hand corner to change the order
the tables. Click on the up button to move a table up in the order and the down button to move it down. Whenever you change the order of the
tables you are directly
affecting the SQL statement that XpertQuery™ is creating, so make sure there is a reason you are
the tables around and that there is still a logical flow to the order.
order you create for your table will determine the way the tables are
in the Link tab.
In the example above the user has chosen to access the VENDORS and
tables for his query.
The next step in building your query is establishing the relationship between the tables you have selected to access. Tables are linked to each other through a common field. XpertQuery™ will automatically display the links between the tables. Make sure that all tables are linked together. If you see a table floating alone with no links to another table, you may need to go back to the Tables tab and insert a bridge table. If you want to change the flow of the links you can click on one of the tables and change its position on the screen. You can also go back to the Tables tab and change the order of the tables there.
Advanced users can click on a link and delete it by pressing <Delete>
or change the link type by pressing the <Options...> key.
you've made a mistake and need to revert back, press the <Smart
key and XpertQuery™ will restablish all links
Once the relationship between the tables you have selected is established you can pick the fields from each table you want to use in your query. When you double-click on a table name on the right side of the screen a list of all available fields will appear. To add a field to your query, select the field and click on the Add button . The fields you add to your query will appear on the right side of the screen under the "Query Fields" heading. You can remove a field or change the order of fields using the appropriate keys.
In the example below the user is accessing the Style name, Style
Price, Style Order Cost and Vendor name fields.
The Select tab lets you define search criteria you can use to filter out certain records from the fields you've selected. Use this tab to refine a query using boolean logic.
Click on the "WHERE" button to open a drop-down menu logical
you can choose from. If you are using more than one criteria, you can
an "AND" operator to make the criteria inclusive; otherwise use the
operator. If you want to remove a criterion, click on "DELETE."
When you click on the "Table" button you will see a list of tables
you have previously selected in the Tables tab. Find the table you want
click on the "Column" button to find the right field.
Next, click on the "is" button to pick one of two binary operators:
Now click on the "greater than" button to select the filtering
you want to use:
Finally, click on the "Expression" button to select the parameter you want to use in your filter. The "Expression" button will open a drop-down menu with all of the values stored in the field you have selected.
In the example above, the user has selected to exclude all vendors
Vendors_ID is greater than 3. The Vendors_ID field is the internally
consecutive serial number assigned to all vendors in the vendors in the
Vendors Catalog and corresponds to the
the record holds in the table. Hence the vendor with Vendors_ID 3 is
third vendor to appear in the Vendors Catalog.
Note that you can also use paraenthesis to build ever more complex
The last step in building your query is to define any sort criteria you may want to use. The sort criteria determines the order in which the results of your query are displayed. XpertQuery™ will order the results from top to bottom by the fields you select and add to the right side of the screen (under "Group Fields"). Click on the "Sorting" button to determine whether you want data to appear in ascending or descending order.
If you use more than one field to sort your results XpertQuery™ will first use the top-most field appear under the
Fields" heading and use it to sort the data and then will use the
field to order the data within the structure created by the first
and so on. For exmaple, the first sorting might order all results
from A to Z and the second sorting might order all of the data within a
Structured Query Language (SQL) is a standard language used by the vast majority of database programs to send instructions to a database. All queries to the database are made through SQL statements in the same way that all instructions to your computer are actually made through Machine Language in 1s and Os. As you work with the Tables, Link, Fields, Select and Sort tabs what you are actually doing is creating SQL statements. XpertQuery™ takes the instructions you entered in these tabs and translates them into SQL.
The SQL tab displays the SQL statement that you have been building
your query. The SQL statement cannot be edited directly, you must
to one of the prior tabs if you wish to make a modification.
And to think that in the "good old days" you would have had to enter
SQL statements by hand!
This tab displays the results of you query once you have clicked on the Execute button in the toolbar at the top. You can click on a column of data to move it to a different location or resize it.
Using XpertQuery™ as a Merchandise Selector
When you run certain processes in XpertMart™ screen such as exporting data, making mass changes to prices and costs, or modifying large quantities of data using the Replace Where function, the XpertQuery™ screen will usually open up as the last step of the process.
When this happens you will notice that the Database, Tables, Link
Fields tabs are grayed out and cannot edit the information in them.
is because XpertMart™ has
filled these in depending on the context in which you find yourself
XpertQuery™ tool. If you are using XpertQuery™ to filter merchandise after running the Mass
to Prices function, XpertMart™ already knows you
using the PRICES table, and so on.
Otherwise, you can use the Select Tab to filter our merchandise and have the changes only apply to a subset of your merchandise. You will want to manually define the filtering criteria. For example, if you do a Physical Inventory of only the Men's Line of products, this is what you would enter in the Merchandise Selector:
Or for example, you might be making changes in the Prices Catalog that only apply to two brands, Puma and Sketchers:
Or you might be exporting data to a Web Store that is only from one Vendor, Nine West:
If you want the change to apply to all of your data all you have to do is press <OK> without entering any Selecting criteria. When you are done entering your criteria, press <OK> and XpertMart™ will proceed with the process under way.
Copyright © 2002 XpertMart
* Return to Table of Contents*