TOC PREV NEXT INDEX



Set up a database search


Next we will use the Database Editor and the Code Sourcerer to generate SQL statements.

  1. Choose the SQL builder tab of the Database Editor. On the left side of the panel is a list of all the available groups of databases, in a tree format.
  2. Expand the node of the tree named "PUBLIC" to see a list of database tables. If you click on one, you will see all the records in the table.
  3. Click on "Product_Tbl" (found under "PUBLIC"). You can resize the columns in the table, and the Database Editor window, if you have difficulty viewing the information in the table.

Next we will build an SQL query.

  1. Click on the Code Sourcerer button. Since you are using the Code Sourcerer in the Database Editor, a special set of options for generating SQL code will appear. The most common one, "Search for rows from a database table", will be used in this tutorial.
  2. Press Next. The next page lets you choose which table or tables you wish to do your search with.
  3. Select "PRODUCT_TBL" by expanding the "PUBLIC" item and clicking on "PRODUCT_TBL" (You may need to scroll down the tree).
  4. Press Next. The next page lets you decide which columns you wish to display, or if you wish to display them all. In this search we're interested in four columns.
  5. Select "Choose Columns". The tree of the possible columns will now be enabled.
  6. Select "DESCRIPTION", "PRODUCT_NUM", "PURCHASE_COST", AND "QTY_ON_HAND" and then press Next.

The final page will let you refine your search by selecting only certain rows to be returned. For this first search, we are interested in items which are in stock, so the quantity available must be greater than zero.

  1. Choose "QTY_ON_HAND" from the popup list on the bottom left. The query will be updated dynamically as you choose items.
  2. Choose "is greater than" from the popup list in the middle of the bottom row.
  3. Type "0" into the field on the right of the bottom row. The SQL code for your query appears above. It should now read
QTY_ON_HAND > 0

This is the only item that we will be searching for.

  1. Press done to finish the Sourcerer. (You may, if you wish, have multiple criteria for your searches. You can do so by pressing the Create New button.)

The SQL code for your command has now been entered into the statements area. It should read

SELECT DESCRIPTION, PRODUCT_NUM, PURCHASE_COST,
QTY_ON_HAND
FROM PUBLIC.PRODUCT_TBL
WHERE QTY_ON_HAND > 0
  1. Press the execute query button to see the results of your search. You must execute each query at least once.
  2. Change the name of the query from "query1" to "inStock" by typing the new name in the "Statements:" combobox at the top of the SQL Builder page.

You have now created an SQL search which has successfully queried a database and produced results.


Data Representations, Inc.
http://www.datarepresentations.com
support@datarepresentations.com
sales@datarepresentations.com
TOC PREV NEXT INDEX