RunQuery Use Examples
Basic Use Example
Under the “Database Services”, expand “RunQuery”. You will see some options related to the saved database configurations. These will be populated based on the existing instances under the “Database” configuration in the configuration variables.
Drag over the JDEData option to the Process Canvas. This will create a “RunQuery” Service on the Process Canvas. Grab one of the connector nodes from the “Start” service and connect it to your newly added “RunQuery”. Now double click the service to configure it.
Since we dragged the RunQuery from a saved Database Configuration, the DBType and ConnectionString are already filled out with variables pointing to the option we selected. Had we dragged over the RunQuery node itself and not one of the database configurations under it, these would still be blank.
The RunQuery’s Query parameter takes the full SQL query as input.
In order to configure the service, only the DbType, ConnectionString, and Query needs to be entered. As you use the RunQuery service more, you could type out your query without using additional tools. However, if you are unfamiliar with the necessary SQL query syntax, you could right-click the “Query” node and select “Query Builder” to help create your query.
Adding Parameters to a RunQuery
In the end we want the query to resemble this:
SELECT * from <xsl:value-of select="$EP_Profile_Database_JDEData_DbName"/>.F0101 WHERE ABAN8=’4242'
- If you used the Query Builder to build the query, you will need to add a Where clause. Add the “WHERE ABAN8=” to the end of the query that was created by the Query Builder in the “Query” parameter of the RunQuery.
- Right click “Parameters” and select “Add New Parameters”. This will create a parameter with the nodes Name, Value and Datatype below it.
- Type “Address” in Name. This is the name you decide to describe your parameter. The name you choose will need to be referenced in the query so EASYProcess knows where to put the value.
- Type “4242” in Value
- Click into Datatype and look in the right panel under “Recommended Values”. Expand it to reveal “Valid Db Data Types”. Expand that and find “String”. The String type means the value will be encased in single quotes.
- Drag the “String” type onto the service canvas value input area.
- Now the RunQuery is almost complete. It has most of a query and one parameter (named Addess) defined.
- Finish the WHERE clause of the Query with “@Address”
- The final result will be: WHERE ABAN8=@Address
The “@” character in the RunQuery Query signals EASYProcess to replace it with a parameter with a name matching the text that comes after the “@” symbol.
This query will access the database “JDEData” (using the saved database configuration data). It will run the given query and return a record from the JDE Address Book table where the Address Number is equal to 4242. The RunQuery could be configured to query a local database and other tables or joined tabled. We could also select specific values from the table instead of “*” which selects all.
Using RunQuery with SubQueries
In this example, we we be looking at a RunQuery which was chosen because the SQL query contains subqueries. This differs from the typical query structure other services like Inquire can handle. This means the developer is in charge of the query structure and as always with RunQueries, must parameterize the query themselves.
This RunQuery is named GetCartHeader. It is designed to retrieve information from the user’s shopping cart header. However, it uses a subquery to also find the subtotal of that cart by summing all the lines in the shopping cart details table. Then it wraps all of this so far in another query which sums the Tax, Shipping, and Subtotal in order to create the TotalAmount.
Notice how all inner queries reference the outermost query’s “CartId” value. Then in that outermost query, the Where clause references the specific CartId we want this to be calculated for (the user’s current cart). This is the query’s only parameter and it is named “CartId”.
Using RunQuery with a Union
RunQuery is a good tool to use when you want full control over your query. Unions are one example where the query structure could still be maintained by EASYProcess in another service, but it might make it visually easier to understand in a RunQuery.
See this query below. It was intended to retrieve the items on an order in JDE. This is a little more difficult than a regular query, because after a certain amount of time, when an order has already been processed, it is no longer in the regular order details table (F4211). The records are moved to the order detail history table (F42119). This means, in order to have one query which returns the lines on an order and have it work for archived orders as well as new orders, we must use a union.
The interesting thing about this query structure is that if we edit it a little bit, it can be maintained in EASYProcess. This is because the WhereClause applied to both sides of the union is the same. So, it could be condensed and eliminate the need for developer maintained parameterization.
SELECT [SelectClause] FROM [Table1] WHERE [WhereClause]
SELECT [SelectClause] FROM [Table2] WHERE [WhereClause]
[Table1] UNION [Table2]