SQL Wizard - Records to include in query
The WHERE statement is how SQL refines the specific information from all the fields you have selected. If you want every single record to be included in the results, you can leave this section blank. Otherwise, you can select the data you want to appear using the Define more criteria options. Use these options to refine what data from the tables you want to review. If you want information on reservations for a holiday weekend, you do not want to have to review the entire table. By defining the criteria, you can review reservations for a certain date or a range of dates.
Selecting the Fields
The first option in the Define more criteria area is where you select the field to be evaluated. Clicking the drop down box will reveal all the columns available from each table that a field was selected from. Even if a column is not going to be displayed as part of the report, the data contained in it can be used to define what data is returned.
Selecting the Criteria
The second option is where you select how you want to define the field selected. The definitions of the items in the drop down box from this section are below. Only the criteria below can be used to define the data.
| Option | Description |
|---|---|
| equals | The value must be the same as ... |
| not equal | The value must NOT be the same as .. |
| greater than | The value must be > than |
| greater than or equal to | The value must be >= |
| less than | The value must < than |
| less than or equal to | The value must be <= |
| is between | The value must be, and include, the values entered |
| is not between | The value must NOT be between the values entered |
| is NULL | The field in the database has NO VALUE at all, and is used to indicate a NON-DATE since DATES must be something. |
| is NOT NULL | The field has a value in it, which includes blank and zero |
| is in list | Must be one of the values listed in a manually entered list. Each value must be separated by commas. |
| is not in list | Will return all the values that are not in a manually entered list. Each value must be separated by commas. |
If the field is a DATE or STRING field, the values must be entered using single quotes.
If the field is a NUMERIC field, the values are entered as-is without single quotes.
Example: 'Robert','Jim','John','Mary' 100,101,102,103 '2006-12-31','2007-12-31' |
| Option | Description |
|---|---|
| is a weekday | Is a Monday through Friday (dates only) |
| is a weekend | Is a Saturday or Sunday (dates only) |
| prompt for date range | The system will place on a single window both a Lower and Higher field for the user to enter a date range in to entry fields with an optional calendar picklist. |
Selecting the Data
The last option in the Define more criteria section is where you select the data within the table to be evaluated. The data can be selected explicitly, or the user can be allowed to choose the data during the execution of the query, or a combination of both. Clicking on the drop down box will reveal options for the user to choose the data when the query is run and all the current data in the fields from the tables(s) selected.
Example of explicitly selecting the data - where only the records for January 1, 2007 will appear:
ACCEXPT.AUDITDATE equals 2007-01-01
Example of allowing the user to pick during execution - where only the records for the audit date that the user picks will appear:
**ACCEXPT.AUDITDATE equals {?Enter Audit Date} **
Next Condition And/Or
When entering multiple WHERE conditions (more than one), you have the option that the next condition being entered will use either AND or OR logic.
State = 'AL' AND City = 'Jacksonville'
State = 'AL' OR City = 'Jacksonville'
In addition to standard "And/Or" logic, you have the ability for advanced conditions by highlighting the line and selecting the "And/Or" button. This allows you to do conditional grouping:
(State = 'AL' AND City = 'Jacksonville') OR (State='FL' AND City = 'Jacksonville')
Note: The And/Or button will insert a record, unless the ) is selected. If there is only one condition, the And/Or logic would not be relevant.