Query SQL Data Tables Generated By DBX

One of the data sources that DBX support is MS SQL database.  Users can query data using standard MS SQL queries.

DBX generates SQL tables when it retrieves data from another data source such as a text file or from a Bullhorn data source task.

For example, the first 2 tasks (281, 282) retrieves data from Bullhorn and 2 new SQL tables are generated, named “data_281” and “data_282”.

The task Id 283 executes a query that compares data in both data_281 and data_282 which results to a new table named data_283.

So how do we use a SQL Data Source Task?

Step 1 – Add a new SQL Data Source Task to an existing process.

Step 2 – Define Task Description

Use any description that properly describes the intent of the query

Step 3 – Define Data Source Settings

First, select “DBX Database” from the data source connection drop down list.

Set the query that needs to be executed to retrieve the desired data.  In this example, we are using the following query:

select opportunity.id,notes.id as notesid,opportunity.customText1 as Old_Action,notes.action as New_Action from data_282 opportunity
inner join data_281 notes on cast(notes.id as bigint) = cast(opportunity.notes_id as bigint)
where opportunity.customText1 <> notes.[action]

In plain English, the query above simply says “Return all opportunities where the latest note action value does not match the opportunity customText1 field value”.  The result of this query can then be used to update the opportunity with the correct notes action value.

Step 4 – Save and Close.

endusersoftware has written 25 articles