User Guide


Skip to end of metadata
Go to start of metadata

SQLManager  is an integral part of all MarketWide's programs and is accessible from all the MarketWide components. It helps maintain MarketWide and database objects such as tables, views, and cubes; selects a database and table within a MarketWide program, and queries your databases. However, the most prominent use of SQLManager will simply be to select databases and tables to run campaigns and queries against.

Topics

Getting Started with SQLManager

The primary purpose of SQLManager is to make it easy to select databases and tables for use in other MarketWide modules. Additionally, SQLManager has several other useful features. On the Database tab, in addition to choosing a table, you can view the table with QueryBuilder, create or edit a join, union, or query; or you can perform a limited number of functions on your tables. On the MarketWide tab, you can view the content of a cube in QueryBuilder, create a new cube, or edit an existing cube.

Choosing a Table

SQLManager displays the servers you are logged on to, the databases available for each server, the owners of each object, the objects available within each owner, and the fields within each object. Before you can run a query, you must select an object with SQLManager.  

To Select a Database and Table

  1. From any MarketWide program, click the SQLManager () button on the toolbar or choose Query > SQLManager from the menu.
  2. Double-click on the name of the server, then the database, and then the table owner to expand each of these items.
  3. Highlight the table you want to use in the left pane of SQLManager. A list of fields displays in the right pane.
  4. After choosing a table, click OK.

Viewing Table Contents

Viewing table contents is easy and fast. With one mouse click, SQLManager opens QueryBuilder, which displays the contents of any table.

Viewing Table Contents

  1. In either the right or left pane of SQLManager, highlight a table.
  2. Click View Table (  ) or select View > View Table from the menu.

Time Saver

  • You can also use the up and down arrows on the keyboard to select objects in SQLManager and the <Enter> key to expand selected objects. Additionally, rather than clicking OK, you may right click on the table to choose the query application that best suits your needs.
  • You can use the Cancel button to exit SQLManager without selecting a table.

The SQLManager Options Dialog Box

The right pane of SQLManager displays detailed field information in selected table cells. You can choose which columns to display using the Field Options feature.

To Edit Field Options

  1. Select Tools > Display Options from the menu.
  2. In the SQLManager Display Options dialog, click the check-boxes to select the columns you wish to view or remove the check mark to hide a column from view.
  3. Click OK to confirm your changes.

Printing Output

MarketWide uses a standard print dialog to print query output. From the print dialog, you can access the Page Setup options.

Page Setup 
When printing QueryBuilder output, you can customize the format and settings of your output to enhance your reports. Adjusting scaling, changing fonts, adding dates, and margins, gives an abundance of presentation options.

Access the Page Setup dialog by clicking on File > Print from the menu in the QueryBuilder output, and then clicking File > Page Setup from the print preview page. .

There are four main sections in the Page Setup dialog: Paper, Margins, and Orientation. 

    • Paper – In the Paper dialog, you can select the paper type and source. 
    • Margins in Inches– In the Margins dialog, you can adjust page margins. A preview is displayed in the preview pane.
    • Orientation – The Orientation dialog allows you to choose either Portrait (vertical) or Landscape (horizontal) orientation. Landscape is particularly useful for wide report outputs.

The SQLManager Menu and Toolbar

The SQLManager toolbar provides shortcuts to commonly used SQL commands. Click an icon once to carry out the action represented by that icon.

Icon

Description

Menu Equivalent

Submits a refresh request to the host for the database.

File > Refresh Orders

Opens the Print dialog, allowing you to specify printing options such as printer selection and the number of copies printed. Clicking the properties button within this dialog allows you to adjust page options such as orientation as well.

File > Print

Exits the SQLManager module.

File > Exit

Selects all the components of the highlighted object.

Edit > Select All

Copies the currently selected object.

Edit > Copy

Copies the currently selected object and the headers to paste into a spreadsheet.

Edit > Copy with Headers

Deletes the currently selected object.

Edit > Delete

Allows the user to edit the selected object.

Edit > Edit

Allows the user to rename the selected object.

Edit > Rename

Opens JoinBuilder to create a new join.

Edit > New > Join

Opens UnionBuilder to create a new union.

Edit > New > Union

Opens QueryBuilder to create a new Query.

Edit > New > Query

Allows the user to expand all the objects under the criteria tree.

Edit > Expand All

Allows the user to collapse all the objects under the criteria tree.

Edit > Collapse All

Opens the appropriate module to edit the currently selected object.

Edit > Properties

Opens frequency distribution dialog on the currently selected field.

View > Frequency Distribution

Opens the selected table.

View > View Table

Opens the DataExport application to transfer data to text files, and then to the database.

View > Export

Sends the selected object into the QueryBuilder program.

View > Send to > QueryBuilder

Sends the selected object into the Analyzer program.

View > Send to > Analyzer

--

Selecting this option causes MarketWide applications to display the Table Change dialog when switching tables during report creation.

Tools > Prompt on Table Change

--

Opens the options dialog allowing you to select what descriptions you wish to be displayed for each field in a table.

Tools > Display Options


The Table Change Dialog Box

Follow the instructions in Choosing a Table to select a table at any time. For example, suppose you begin entering criteria for a report using the Sales table, only to realize that you should be using the Products table. SQLManager allows you to switch to the Products table, even though you have begun entering report criteria.

Caution

While convenient, the ability to change tables at any time has a side effect. By default, MarketWide applications discard all query and filter options when switching tables during report creation. Your options are not discarded when using secondary tables. Save often to avoid accidental loss of work.

The Table Change dialog offers the opportunity to use your original criteria in the new table. If you want to use your original query and filter settings when you switch tables, activate the Prompt on Table Change option under the Tools menu in SQLManager. Enabling this option causes the Table Change dialog to appear automatically every time you select a new table while creating a report. The Table Change dialog offers several options:

    • Clear my Query – Clears all report criteria except for filters when changing to the new table.
    • Clear my Filter – Clears all filters but leaves other query criteria intact when changing to a new table.
    • Use the current settings and don't ask me this question again. – Performs currently selected actions and ensures that the Table Change dialog will not appear again.

Note

  • If you click Use the Current Settings and Don't Ask Me This Question Again option and later want to change the Table Change dialog settings, you can turn the Prompt on Table Change option back on in SQLManager at any time.
  • Select Clear my Query and Clear my Filter to start fresh when switching tables.
  • Deselect Clear my Query and Clear my Filter to retain all original report criteria when switching tables.

Frequency Distribution 

The Frequency Distribution drill-down performs a count on each value of a selected field, and then creates a report that can be saved or exported for later use.

To Use the Frequency Distribution Drill-Down

  1. In SQLManager, highlight a table in the left pane to view its fields in the right pane of the dialog.
  2. Right-click a field in the right pane of SQLManager, and select Frequency Distribution.

Note

Keep in mind that the Frequency Distribution drill-down may require more processing time for fields with many values. It is recommended that you do not perform a Frequency Distribution on a unique field, or a field that has more than 100 unique values, such as 'Customer Number.'



Field Information

The Field Information drill-down retrieves some basic information, such as minimum and maximum field values, as well as the number of distinct values present in the field on that particular table.

To Use the Field Information Drill-Down

  1. In SQLManager, highlight a table in the left pane to view its fields in the right pane.
  2. Right-click a field in the right pane of SQLManager and select Field Information.
  3. Save, copy, or print the information from the InfoViewer window.