The Criteria Query screen gives you the simplest way to retrieve and update information from your database. Essentially, you can do it all without having to write a line of SQL.
Pick the right criteria query screen to open
There are two ways to access criteria query functionality. You can open a new Criteria Query screen from the Home menu or Start Page. Or, you can right click on any table in the Object Browser and select “View\Edit Data” or “Select Data”. The only difference with accessing the screen through the Object Browser is that you won’t be able to manually select a different table.
Side Note: View\Edit Data will open a criteria query form and automatically retrieve the table contents for you. Select Data will open a criteria query form but not execute anything until you’ve entered criteria.
Filling out the form
Assuming you have a data source already connected, the first thing you’ll do when the screen opens is find and pick the table you want to query or update. To find your table quickly, you can type in the search field above the list of tables.
Clicking on the table will display its columns in a form view. The [=] icon between the column name and field is clickable; Clicking this icon will display the different equality operators that you can use. Pick one from the list that’s applicable to the query you want to execute.
Next, enter a value in one of the column fields. For char or varchar values, just enter the text with no quotes. If you’re using the Like operator, you’ll need to add the “%” sign appropriately. Once complete, you can go to the “SELECT Statement (read only)” tab to verify your SQL statement (if you wish to do so).
Retrieving and updating data
Once everything is in place, click the Run toolbar button (first button in the toolbar). Your SELECT will execute and results will retrieve in a new tab. The results returned are fully editable. Use the toolbar buttons to insert or delete rows, or to bulk import from a text file. Modify existing data in cells as you wish and once done, click the Update toolbar button to commit your changes to the database.
In a future post, we’ll talk more about the results tab and all the cool things you can do with it (including; generating new tables from data, generating insert statements, sorting columns, creating graphs, copying data, etc).