Skip to main content

Stranded Query

Creating Standard Queries​

To create a standard query, you must already have created at least one data connection within a DPL Studio package. Once the data connection is established, you can begin creating a standard query in one of two ways:

Go to the Models and select New Standard Query

Once created, standard queries can be refined in either design (drag-and-drop) or advanced (native language) mode.

Before switching from design to advanced mode, it is advised you save the query. (From the File menu, select Save [query name] where [query name] is the name of the current query). Once you are in advanced mode, clicking to design mode will clear the current query. You can only reload the query by clicking on its name in the Data pane if you saved the query before switching to advanced mode.

Creating Standard Queries in Design Mode​

Design mode is the default for standard queries and the only mode available for aggregation queries. The design mode presents a visual abstraction of tables and joins. When you work with a query in design mode, the right side of the query editor lists the tables, views, and stored procedures found in the data connection that is serving as the source for the query. The query editor's other panes reflect your work as you build the query

The content pane (top left side of the editor) displays the elements of your query as you build it.

The native language pane (center-right) displays the query you are designing in the native-language of the data connection.

The preview pane (lower-right) displays a sample of the data being returned by your query.

The graphic shows the query editor as it appears when a standard query is first opened in design mode. Except for the portion listing elements found in the source data connection, the editor's panes are blank because no elements have yet been selected or refined.

To create a query in design mode

  1. Choose the appropriate connection from the Connections drop-down in the toolbar.

  2. Select the tables or procedures that contain the desired data by clicking and dragging the desired object to the content pane.

If a table contains Phantom in the title and has a gold title bar, the table no longer exists due to a change in the connection or the underlying database. If you are certain the query is correct, phantom tables can still be used and joined across. If you delete a phantom table, however, it cannot be retrieved again.
  1. DPL Studio caches the table and column metadata for all connections used inside the DPL package to avoid extraneous queries to the database.

To see how recent cached date is, hover over a table name in the Tables pane

To get new table metadata, use the Refresh Tables tool on the toolbar to update it.

To refresh column metadata, right-click on a table in the Tables pane and select Refresh Columns from the shortcut menu. If the table is already being used, it will not update automatically to prevent loss of the current query. If you wish to use the new column metadata, close the table and drag it over again.

If you are using multiple tables, joins can be created by dragging a column from one table onto a column of another table, automatically creating an inner join where the two columns are equal.

To change the join type or condition, right-click on the join line and select the type and condition from the shortcut menu.

To change the join order, the relationship between joins can be set from the same shortcut menu. Use the query in the native language pane to determine what the join order is.

  1. Select the desired fields/columns from the table, using the small dialog that appears when the table is positioned in the content pane.

To include all values from a particular field in your query, click the check-box to the left of the field name.

To include all values of all fields, click the * in the lower left corner.

Right mouse click on title bar of the table you will find

Sort Column: Select the required order, using the small dialog that appear

Show Column:Select the required option, using the small dialog that appear to show relative columns.

  1. Refine the data selection from each field by creating filters, groups, and order conditions. Right-click on the desired field and select Add Filter, Add to Group, or Add to Order from the shortcut menu.

Add to Order opens a secondary menu allowing you to choose whether the contents of the field should be presented in Ascending or Descending order.

Add to Group adds the selected field to a group list maintained for this query. If you add more than one order or group condition, all will appear in a list on the Filter Condition dialog's Group Conditions or Order Conditions tabs.

Add Filter open a conditions dialog allowing you to specify the details of the condition

.

The first item in the condition is the specified field, in the form Table.Field.

The second item in the condition is the operator, which is set to = (equals) by default. To choose a different operator, right-click the = and select the desired operator from the shortcut menu (choices include Not, Like, In, Between, and Is Null). Note that the operator Not can be combined with any other operator.

The final part of the condition contains one or more text boxes, depending on the operator. Click on a text box and type the appropriate value. Some connectors will attempt to automatically format values to the correct syntax although it is still possible to enter a value that will not create a valid query. Parameters can be used by providing a parameter name within braces . When the query is run, DPL Studio prompts for the parameter value, then substitutes that value in place of the parameter. If you are having difficulty getting a parameter value to work, consult the type formatting documentation for that specific database.

  1. Continue adding filters until you have achieved the desired query. You can also adjust filter relationships and use toolbar icons to check the progress of your query-building:

To change the filter order, click and drag the filter list item to the appropriate position on its filter conditions tab.

To remove a filter item, click the red “X” to its right.

To determine how much data your query will return, click the Row count icon on the toolbar.

  1. You can preview the results of your query by clicking the preview icon on the toolbar. The data returned by your query appears in the preview pane.

To adjust the amount of data returned in the preview, use the Start row and Max row sliders above the preview pane.​

For other adjustments and checks, use the toolbar icons:

Row Count: number of rows of data the full query will return

Conditions: edit the query's conditions

Parameters: edit the query's parameters

Smart Join: Digital Process Logic Studio identifies and creates appropriate joins between the tables based on matching column names

  1. Save the query by going to the File menu and selecting Save [query name] where [query name] is the name of the current query.
When you finish working on a query in design mode and want to perform more edits in advanced mode, save the query before switching from design to advanced mode. This creates a save point to which you can revert if you are not satisfied with the changes you make in advanced mode. Once you are in advanced mode, clicking to design mode clears the current query. You can only reload the query by clicking on its name in the Data pane if you saved the query before switching to advanced mode.

Creating Queries in Advanced Mode In advanced mode, queries are represented in the native language of the data connection. SQL is the language for most databases; however, the language can vary by the connector. Some connectors such as XML and text do not have a native query language and therefore do not support using advanced mode queries.

To create a query in advanced mode:​

  1. Create a new standard query. You may begin to define conditions of the query in design mode, but it is not required.
When you finish working on a query in design mode and want to perform more edits in advanced mode, it is advised you save the query before switching from design to advanced mode. This creates a save point to which you can revert if you are not satisfied with the changes you make in advanced mode. Once you are in advanced mode, clicking to design mode clears the current query. You can only reload the query by clicking on its name in the Data pane if you saved the query before switching to advanced mode.

  1. Slide the Query mode from Relational Query to Advanced Query. The content area of the editor now allows you to type the query text in SQL or other native query languages. For reference on SQL or other query languages, consult the documentation for that specific database.

  2. The Connections drop-down list in the toolbar displays the connection currently being used. You can freely change connections while editing the query. Note that query syntax is generally not compatible between different connectors, and switching to a connection that does not support advanced queries will clear your current query and convert it to a design query.

  3. Using MySQL Server Cache Server we can Create Query like SELECT [Tablename.Column name] FROM[Tablename] in Advanced Standard Query.

Using the Expression Editor​

The DPL Expression Editor provides a way to manipulate data into new forms. For any given connector, the expression syntax and functions available are those native to the connector being used in the query. (For example, when querying from Oracle only Oracle-native syntax and functions can be used.) The DPL expression syntax is used with text and XML connectors and in transform queries. Specific functions and the DPL expression syntax used to combine them are covered in their own sections.

The editor appears in two places within DPL, serving two different purposes:

The standard query editor (available in standard and aggregation queries) allows you to append custom columns to a table.

The transform query editor (available only in transform queries) produces a new table.

In either case, the process of creating a query is the same. While you can type expressions directly when working in Advanced Mode, DPL provides an Expression Editor for easier editing within Design Mode.​

To use the Expression Editor

  1. Open the desired standard query in the document window by double-clicking the query in the Data pane or right-clicking the query in Data Flow and selecting Open from the shortcut menu.

  2. Click the "+" sign in the lower right corner of the table you wish to use in the expression in order to open the Expression Editor.

  3. An Expression Editor dialog appears.

  4. Drag and drop the desired tables, fields, and functions to the text box at the bottom of the editor to construct the desired expression. Type in the appropriate DPL expression syntax or native syntax to connect the drag-and-dropped elements.

You can also type all expression elements, values, and syntax directly into the text box.

  1. When the expression is complete, click OK to return to the query editor screen.

  2. When you are satisfied with the query, go to the File menu and select Save [query name] where [query name] is the actual name of your query.

Creating Reusable Queries​

A Resuable Queries deals directly with the meta data from a standard query. In their turn, Data Models can become the data sources for aggregate and transform queries.

  1. Create a standard query and save the query.

  2. Once the standard query is created, you can begin creating a Data Model.

info

Right mouse click on standard Query node and select Create Resuable Queries option from shortcut menu as shown below.

info

After selecting the Create Reusable Queries from shortcut menu, Create Reusable Queries wizard got opened as shown below.

  1. Specify the required parameters.

  • Filename- By default it will display the path to the application directory, but we can change the file name by clicking on (...) browse button.

  • Data Model Name- By default it will take the standard query name and if we want to change the data model name we can change it.

  • Give valid values to the remaining fields (Description,Application and category) in the create data model wizard.

  • Description field meant by, description regarding the data model.

  • Application field meant by, the type of Application like connection type (SAP, SAP B1 ... etc) by giving the application name it will put the related data model under this name of the application.

  • Category field meant by, type of category of data model like (Inventory, Business Partners ... etc) by giving the category name it will put the related data models under this name of the category.

  1. Click on OK button it will create the data model as a file with the file extension .dm .