Runs a query against a table in a database, and if multiple results are returned, delimits them with pipe-bar characters.
The QueryData function retrieves database data from an ODBC data source or directly from a SQL server database.
Using the QueryData wizard, DriveWorks allows you to connect to existing data within your company.
The wizard walks you through the database connection, assists in the selection of the table or view from which to pull the data, and then helps sort and filter the data accordingly.
The connection to the database is live at all times, so retrieving very large amounts of data could impact the response time back to the user.
QueryData( ["Connection String"] , ["SQL Command"] , ["Username"], ["Password"] )
The QueryData wizard produces the query language required to connect, filter and display the data. The following explains each step required.
Connection String
QueryData supports both DSN and direct SQL connection strings.
Using the QueryData wizard select the type of connection and then follow the wizard to make the connection string.
SQL Command
Is a valid SQL Query string that can filter, group and sort the data being queried. Note the DriveWorks QueryData wizard will build this query string.
Username
The username to gain access to the database (optional)
Password
The password to gain access to the database (optional)
The QueryData wizard will ask for various pieces of information to build up the SQL String to filter, group and sort the data being queried. This includes the following information:
The table that contains the data in the database
The field in the table the data is to be retrieved from
Select the type by which the data requires to be grouped
| Group by type | Data Format | Description |
|---|---|---|
| Max | Numerical Only | Groups the records with the highest value |
| Min | Numerical Only | Groups the records with the lowest value |
| Count | Numerical Only | Returns the quantity of records that would be displayed from the query |
| Distinct | Any Format | Returns only unique records from the field. Duplicates are removed. |
The SelectStatement allows selection of any field from the selected table and compares it to any value or parameter in your DriveWorks project.
The SelectStatement filters the data to be returned into DriveWorks.
Available comparison operators from the wizard include:
| Comparison Operator | Description |
|---|---|
| = | will filter all the data where the table field matches exactly the value |
| <> | will filter all the data where the table field does not match the value |
| LIKE | will filter all the data where the table field contains the entered value. The LIKE operator can make use of wildcard symbols placed in the value field once finish has been clicked in the QueryData wizard. The wildcard symbol, for SQL Query language, is the % symbol. So for example after running through the QueryData wizard you have a where clause like: WHERE [CustomerName] LIKE '" & LetterReturn & "' The wildcard symbol should be placed in between the end quote and apostrophe: WHERE [CustomerName] LIKE '" & LetterReturn & " %' |
The returned data can be displayed in ascending or descending order based on any other field in the table.
Select ASC for ascending order or DESC for descending order.
See also