DriveWorks Pro 15: QueryData [send feedback...]

QueryData

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.

This function will only re-evaluate itself when a value used within one of its arguments changes. Please see How To: Force views from a database to refresh when the data has changed. for more information.

Syntax

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)

QueryData Wizard

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:

Table

The table that contains the data in the database

Field

The field in the table the data is to be retrieved from

Grouping

Select the type by which the data requires to be grouped

Group by typeData FormatDescription
MaxNumerical OnlyGroups the records with the highest value
MinNumerical OnlyGroups the records with the lowest value
CountNumerical OnlyReturns the quantity of records that would be displayed from the query
DistinctAny FormatReturns only unique records from the field. Duplicates are removed.

SelectStatement

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 OperatorDescription
=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 & " %'

SortByField

The returned data can be displayed in ascending or descending order based on any other field in the table.

SortingOrder

Select ASC for ascending order or DESC for descending order.


See also

How To: Troubleshoot SQL Connection

Table of Contents